Help for a Select query!! - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help for a Select query!!

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-08-30 : 13:32:05

Help for write a query on

[ Transaction Table ]

ID Item Fld1 Fld2
1 AA 2 100
1 AB 5 120
1 AC 4 130
2 BA 3 120
2 BC 2 340
3 AB 2 135
3 CA 4 240
3 CC 2 400
3 CB 3 100

i need help to generate a query out put, like -

1 AA 2 100
2 BA 3 120
3 AB 2 135

first record of each group ( its by ID)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:43:14

What is "first" in group?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:46:21

[code]DECLARE @Sample TABLE (ID INT, Item VARCHAR(2), Fld1 INT, Fld2 INT)

INSERT @Sample
SELECT 1, 'AA', 2, 100 UNION ALL
SELECT 1, 'AB', 5, 120 UNION ALL
SELECT 1, 'AC', 4, 130 UNION ALL
SELECT 2, 'BA', 3, 120 UNION ALL
SELECT 2, 'BC', 2, 340 UNION ALL
SELECT 3, 'AB', 2, 135 UNION ALL
SELECT 3, 'CA', 4, 240 UNION ALL
SELECT 3, 'CC', 2, 400 UNION ALL
SELECT 3, 'CB', 3, 100

SELECT ID,
Item,
Fld1,
Fld2
FROM (
SELECT ID,
Item,
Fld1,
Fld2,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Item DESC) AS RecID
FROM @Sample
) AS d
WHERE RecID = 1
ORDER BY ID[/code]

E 12°55'05.25"
N 56°04'39.16"

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-08-30 : 13:50:10

quote:
Originally posted by Peso

What is "first" in group?

E 12°55'05.25"
N 56°04'39.16"

I only mean the first occurrence of ID and that Record. Please see the expected output !!
Corresponding to ID = 1 there is 3 Record and ID =2 => 2 records and ID =3 => 4 Records..

I want to fetch the First row of ID=1 and ID=2 and ID =3

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:53:12

How do you decide which record is "first" within the group of records with ID 1?
There exists no order in a relational database!

E 12°55'05.25"
N 56°04'39.16"

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-08-30 : 13:57:17

My Database is in SQL SERVER 2000 - when i tried this Query it says
"'ROW_NUMBER' is not a recognized function name."!!!

mmm.... what to do...!!!

quote:

Originally posted by Peso

DECLARE	@Sample TABLE (ID INT, Item VARCHAR(2), Fld1 INT, Fld2 INT)

INSERT	@Sample
SELECT	1, 'AA', 2, 100 UNION ALL
SELECT	1, 'AB', 5, 120 UNION ALL
SELECT	1, 'AC', 4, 130 UNION ALL
SELECT	2, 'BA', 3, 120 UNION ALL
SELECT	2, 'BC', 2, 340 UNION ALL
SELECT	3, 'AB', 2, 135 UNION ALL
SELECT	3, 'CA', 4, 240 UNION ALL
SELECT	3, 'CC', 2, 400 UNION ALL
SELECT	3, 'CB', 3, 100

SELECT		ID,
		Item,
		Fld1,
		Fld2
FROM		(
			SELECT	ID,
				Item,
				Fld1,
				Fld2,
				ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Item DESC) AS RecID
			FROM	@Sample
		) AS d
WHERE		RecID = 1
ORDER BY	ID

E 12°55'05.25"
N 56°04'39.16"

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 13:59:51

You could begin by posting in proper forum.
This is SQL Server 2005.

Hopefully a moderator will relocate this message to a SQL Server 2000 forum.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 14:01:44

[code]DECLARE @Sample TABLE (ID INT, Item VARCHAR(2), Fld1 INT, Fld2 INT)

INSERT @Sample
SELECT 1, 'AA', 2, 100 UNION ALL
SELECT 1, 'AB', 5, 120 UNION ALL
SELECT 1, 'AC', 4, 130 UNION ALL
SELECT 2, 'BA', 3, 120 UNION ALL
SELECT 2, 'BC', 2, 340 UNION ALL
SELECT 3, 'AB', 2, 135 UNION ALL
SELECT 3, 'CA', 4, 240 UNION ALL
SELECT 3, 'CC', 2, 400 UNION ALL
SELECT 3, 'CB', 3, 100

SELECT s.ID,
s.Item,
s.Fld1,
s.Fld2
FROM @Sample AS s
INNER JOIN (
SELECT ID,
MIN(Item) AS minItem
FROM @Sample
GROUP BY ID
) AS d ON d.ID = s.ID AND d.minItem = s.Item[/code]

E 12°55'05.25"
N 56°04'39.16"

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-08-30 : 14:08:11

Yes you are right! There is no field keep an order! i just want to select all records , thats the first row of every new ID

quote:
Originally posted by Peso

How do you decide which record is "first" within the group of records with ID 1?
There exists no order in a relational database!

E 12°55'05.25"
N 56°04'39.16"

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

Aiby
Yak Posting Veteran

71 Posts

Posted - 2007-08-30 : 14:15:08

Yes this Works fine!! Let me try out !! Thanks a lot..
Sorry I didnt noticed its for 2005 Server only..
Any way.. Thank you Peso!

quote:

Originally posted by Peso

DECLARE	@Sample TABLE (ID INT, Item VARCHAR(2), Fld1 INT, Fld2 INT)

INSERT	@Sample
SELECT	1, 'AA', 2, 100 UNION ALL
SELECT	1, 'AB', 5, 120 UNION ALL
SELECT	1, 'AC', 4, 130 UNION ALL
SELECT	2, 'BA', 3, 120 UNION ALL
SELECT	2, 'BC', 2, 340 UNION ALL
SELECT	3, 'AB', 2, 135 UNION ALL
SELECT	3, 'CA', 4, 240 UNION ALL
SELECT	3, 'CC', 2, 400 UNION ALL
SELECT	3, 'CB', 3, 100

SELECT		s.ID,
		s.Item,
		s.Fld1,
		s.Fld2
FROM		@Sample AS s
INNER JOIN	(
			SELECT		ID,
					MIN(Item) AS minItem
			FROM		@Sample
			GROUP BY	ID
		) AS d ON d.ID = s.ID AND d.minItem = s.Item

E 12°55'05.25"
N 56°04'39.16"

Aiby Mohan Das
Analyst Programmer
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources