SQL Query Limit Results By Values In Another Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Limit Results By Values In Another Field

Author

Topic

teewan
Starting Member

5 Posts

Posted - 2008-08-18 : 22:52:00

How do I write SQL for taking the top 2 results for the below table

Field 1 : GroupName
Field 2 : TransDate

The sample data could be {(HR, 2008/JAN), (HR, 2008/FEB), (HR, 2008 MAR), (MANAGER, 2008/AUG), (MANAGER, 2008/DEC)}

I wanted to pick the first two values for each GroupName sorted by TransDate ascending.

So if I wanted 2 results for each GroupName, the result would be
{(HR, 2008/JAN), (HR, 2008/FEB), (MANAGER, 2008/AUG), (MANAGER, 2008/DEC)}

This will exclude the third set of data (HR, 2008/MAR).

Any help would be much appreciated...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 23:47:29

what's the datatype of TransDate?

teewan
Starting Member

5 Posts

Posted - 2008-08-19 : 00:05:35

It doesn't really matter...
It could be a numeric, date/time, string...

As long as I can take a certain LIMIT of the sorted order for each of the GroupName...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 00:14:20

it will matter as you want to get results based on order of date.Are you using sql 2005?

teewan
Starting Member

5 Posts

Posted - 2008-08-19 : 00:18:05

Hi, actually I'm using MySQL for this...
I'm just trying to get an idea how to construct the SQL...

To make it easier, perhaps assume Field2 is an Integer...

So for sample of {(AAA, 0), (AAA,1), (AAA,2), (BBB, 3), (BBB,4)}
I wanted to get the first two lowest numbers for field two for each GroupName.

Eg my result would be (AAA,0), (AAA,1), (BBB,3), (BBB,4)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:05:19

In sql 2005, you've function called ROW_NUMBER to achieve this. it will be something like

SELECT t.*
FROm
(
SELECT ROW_NUMBER() OVER (PARTITION BY GroupName ORDER BY TransDate)AS Seq,*
FROM YourTable)t
WHERE t.Seq <=2

teewan
Starting Member

5 Posts

Posted - 2008-08-19 : 04:34:05

Hi Visakh16, seems like your suggestion works by using "OVER" and "PARTITION BY", with PARTITION BY being the main idea to make it work.

Although I cannot really use this feature since I'm using MySQL, it was a good post which I hope will help others...

If there was any other "generic SQL" way of doing this, would be very helpful...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 05:00:46

another way those not well performing:-

select * from
(
select (select count(*) from yourtable where GroupName=t.GroupName and TransDate<t.TransDate)+1 AS Seq,
*
from yourtable t
)r
where r.Seq<=2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-19 : 05:04:02

Why are you not posting MYSQL question over a mysql forum ? There might be an easy way of doing that in MYSQL as in SQL Server 2005 with row_number()

KH
[spoiler]Time is always against us[/spoiler]

teewan
Starting Member

5 Posts

Posted - 2008-08-19 : 20:29:33

Yesterday, I did post the question on MySQL forum.
But the result was not consistent.
However, later on, there was some correction and it was solved!

Thanks Visakh16 for your great help.

Also here is the "generic SQL" solution (I hope it is generic enough!)

SELECT t1.* FROM AAA t1
LEFT JOIN AAA t2
ON t1.groupname = t2.groupname AND t1.indexnumber >= t2.indexnumber
GROUP BY t1.groupname, t1.indexnumber
HAVING COUNT(*) <= 2

MySQL does not require "FROM AAA AS t1", just "FROM AAA t1"

This technique really opens up new possibilities

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 23:55:34

This wont work in SQL Server if you've columns other than groupname, indexnumber in t1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources