Query Help 1

[fmrock]

Hey everyone.

I need some help with a query I am working on

SELECT Loc_Code, Loc_Name, Loc_Number
FROM LOCATIONS
ORDER BY Loc_Code, Loc_Code

There can be multiple Location numbers at for each location.

Here is an example a data

Loc_Code, Loc_Name, Loc_Number
100000,aaaaaaa, 1
100000,bbbbbbb, 2
100000,ccccccc, 3
200000,Name 1, 1
200000,Name 1, 2

Sometimes the Loc_Number starts at 4 instead of 1 for whatever reason. So i want to query up the first record for each Loc_Code.

 

[Lamprey13]

If I understand correctly, this should work for you:

[COLOR=green]--Set up[/color]
DECLARE @Locations TABLE(Loc_Code int, Loc_Name varchar(20), Loc_Num int)

INSERT @Locations
SELECT 100000, 'aaaaaaa', 1 UNION ALL
SELECT 100000, 'bbbbbbb', 2 UNION ALL
SELECT 100000, 'ccccccc', 3 UNION ALL
SELECT 200000, 'Name 1', 4 UNION ALL
SELECT 200000, 'Name 1', 9

[COLOR=green]--Query[/color]
SELECT Loc_Code, Loc_Name, Loc_Num
FROM @Locations l
WHERE Loc_Num = (SELECT MIN(Loc_Num) FROM @Locations loc WHERE loc.Loc_Code = l.Loc_Code)

-Ryan

 

[fmrock]

Thank you