|
Home > Archive > Microsoft SQL Server forum > June 2005 > Top N rows, discard duplicate
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Top N rows, discard duplicate
|
|
| medhanush@yahoo.com 2005-06-22, 3:23 am |
| I have data in table as follows,
Num Category Product
Name
---- -------- -----------
100 A Product1
100 B Product1
101 A Product2
101 B Product2
101 A Product2
101 B Product2
102 A Product3
102 B Product3
102 C Product3
102 D Product3
Can sb pl help to what t-sql query to use to achieve as follows,
for some reason, there are duplicate rows, but i need to get
top most rows of category A, B as follows.
Num Category Product
Name
---- -------- -----------
100 A Product1
100 B Product1
101 A Product2
101 B Product2
102 A Product3
102 B Product3
TIA
MeDhanush
| |
| David Portas 2005-06-22, 3:23 am |
| What does "top most rows" mean? For example, why was "102 C Product3"
excluded from your example result? You can't reliably query rows based on
some notion of order (just the order you wrote them down in?) unless that
order is somehow represented in the table.
--
David Portas
SQL Server MVP
--
| |
| medhanush@yahoo.com 2005-06-22, 11:23 am |
| David,
thxs for the reply.
A is result of first web service call
B is result of second web service call
C is result of third web service call
D is result of fourth web service call
Some times first and second web service are re-submitted.
as a result we have duplicate rows.
And we need to generate report from results of web service responses A
and B.
I'm sure the data is not in Normalized fashion.
Thanks
Kishore
David Portas wrote:
> What does "top most rows" mean? For example, why was "102 C Product3"
> excluded from your example result? You can't reliably query rows based on
> some notion of order (just the order you wrote them down in?) unless that
> order is somehow represented in the table.
>
> --
> David Portas
> SQL Server MVP
> --
| |
| Erland Sommarskog 2005-06-22, 8:24 pm |
| (medhanush@yahoo.com) writes:
> Can sb pl help to what t-sql query to use to achieve as follows,
> for some reason, there are duplicate rows, but i need to get
> top most rows of category A, B as follows.
>
> Num Category ProductName
> ---- -------- -----------
> 100 A Product1
> 100 B Product1
>
> 101 A Product2
> 101 B Product2
>
> 102 A Product3
> 102 B Product3
There is no such thing as a "top-most" row in a table. A table is an
orderd set.
It seems that with the sample data you have given that this would do:
SELECT DISTINCT Num, Category, ProductName
FROM tbl
WHERE Category IN ('A', 'B')
If product names are different, you can do:
SELECT Num, Category, MAX(ProductName)
FROM tbl
WHERE Category IN ('A', 'B')
GROUP BY Num, Category
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| David Portas 2005-06-25, 7:23 am |
| Unless you've stored the information about which came first then you won't
be able to do it. SQL Server doesn't retain that information for you.
--
David Portas
SQL Server MVP
--
|
|
|
|
|