September 30, 2009 at 5:36 am
Hi,
I hv a table called myTable
which hold record like this,
BLOCK_ID TOTAL
1 0
2 19.22641
55 100
55 87.40159
55 62.51066
1 3.219
2 0.099
55 3.219
1 31.0688
2 25
55 34.12
2 0
1 10.73267
2 10.97707
55 10.97707
1 85.94
Can I have top 2 total from each block_id in one single query ?
Thanks
[font="Verdana"]Regards
Kumar Harsh[/font]
September 30, 2009 at 11:17 pm
Hi,
nothing,I trying to display my data better.two column data are separated by =
BLOCK_ID = TOTAL
1 = 0
2 = 19.22641
55 = 100
55 = 87.40159
55 = 62.51066
1 = 3.219
2 = 0.099
55 = 3.219
1 = 31.0688
2 = 25
55 = 34.12
2 = 0
1 = 10.73267
2 = 10.97707
55 = 10.97707
1 = 85.94
[font="Verdana"]Regards
Kumar Harsh[/font]
October 5, 2009 at 1:15 pm
You will probably need to create multiple queries to build up to the result that you want.
I setup a table with your values, then 3 queries; one to retrieve the maximum value for each ID, another to retrieve the 2nd maximum value for each ID, and a third to combine the results of the first two.
The SQL for the first query (qryMax1) is ...
SELECT tmpTable.ID, Max(tmpTable.Total) AS MaxOfTotal
FROM tmpTable
GROUP BY tmpTable.ID;
The SQL for the second query (qryMax2) is ...
SELECT tmpTable.ID, Max(tmpTable.Total) AS MaxOfTotal
FROM tmpTable INNER JOIN qryMax1 ON tmpTable.ID = qryMax1.ID
WHERE tmpTable.Total < qryMax1.MaxOfTotal
GROUP BY tmpTable.ID;
Then the SQL for combining the results of the first two queries is ...
SELECT qryMax1.ID, qryMax1.MaxOfTotal FROM qryMax1
UNION
SELECT qryMax2.ID, qryMax2.MaxOfTotal FROM qryMax2;
You will notice that the second query is filtered to exclude the results from the first. This may give unexpected results if, for example, you have an ID where the two highest values are equal.
HTH
🙂
Chris
October 5, 2009 at 11:41 pm
Hi,
Thanks a lot.
This query also work,
SELECT b.block_id, b.total, count(1) AS RowOrder
FROM mytable AS a, mytable AS b
WHERE a.total>=b.total And a.block_id=b.block_id
GROUP BY b.block_id, B.total
HAVING Count(1) BETWEEN 1 AND 2
ORDER BY b.total DESC;
[font="Verdana"]Regards
Kumar Harsh[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply