471,075 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,075 software developers and data experts.

Top N rows, discard duplicate

I have data in table as follows,

Num Category ProductName
---- -------- -----------
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 ProductName
---- -------- -----------
100 A Product1
100 B Product1

101 A Product2
101 B Product2

102 A Product3
102 B Product3

TIA
MeDhanush

Jul 23 '05 #1
4 3692
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
--
Jul 23 '05 #2
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
--


Jul 23 '05 #3
(me*******@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
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
--
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by TonyB | last post: by
1 post views Thread by Asha | last post: by
12 posts views Thread by Graham Blandford | last post: by
7 posts views Thread by Jon Maz | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.