January 20, 2006 at 5:13 am
Hi All!!
I am trying to write a query in which i have a table with three columns named c_id (integer) , o_id (integer) and o_date (datetime).There can be duplicate values in all the columns but the combination of three values will always be unique.
Now i need to make a query that will give me first 5 distinct values from c_id column while the data is sorted in descending order on o_date column.Can anybody help me in this regard.
Thanks
January 20, 2006 at 5:19 am
If you have data on the following form:
c_id o_date
1 1-1-2006
1 2-2-2006
Then which o_date should be displayed along with c_id = 1? (The largest? The smallest? Or something else?)
January 20, 2006 at 7:10 am
Is it top 5 per date?
if so then
SELECT a.o_date, a.c_id
FROM (SELECT o_date, c_id FROM
GROUP BY o_date, c_id) a
WHERE a.c_id IN
(SELECT TOP 5 b.c_id
FROM
b
WHERE b.o_date = a.o_date
GROUP BY b.o_date, b.c_id)
ORDER BY a.o_date DESC, a.c_id ASC
Far away is close at hand in the images of elsewhere.
Anon.
January 21, 2006 at 12:25 am
Thanks for your reply but i do not need TOP 5 per date.I only need the TOP 5 distinct values for c_id with the records arranged in descending order of o_date.Infact i do not need the date column.I only need the c_id column
January 21, 2006 at 3:33 am
I am still not sure what you want. The problem is the non-uniqueness of the columns. I have two examples below. Please state what the result should be in each of these cases.
c_id o_date
1 '2006-1-1'
2 '2006-1-1'
3 '2006-1-2'
4 '2006-1-2'
5 '2006-1-3'
6 '2006-1-3'
c_id o_date
1 '2006-1-1'
2 '2006-1-1'
1 '2006-1-2'
2 '2006-1-2'
3 '2006-1-3'
4 '2006-1-3'
5 '2006-1-4'
6 '2006-1-4'
January 23, 2006 at 2:22 am
SELECT TOP 5 c_id
FROM (SELECT DISTINCT o_date, c_id FROM @table) a
ORDER BY o_date DESC, c_id ASC
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply