Choosing Unique Record - 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 2000 Forums

Transact-SQL (2000)

Choosing Unique Record

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-08-16 : 22:35:52

Hi all,

I'm having a problem in setting the condition to select record from a group of records. I think it's better to explain using example.

I have a table [TEST]:
CREATE TABLE TEST (EmpID nvarchar(10), Meal nvarchar(10))
GO
INSERT INTO TEST '001', 'Lunch'
INSERT INTO TEST '001', 'Dinner'
INSERT INTO TEST '002', 'Lunch'
INSERT INTO TEST '002', 'Dinner'
INSERT INTO TEST '003', 'Lunch'
INSERT INTO TEST '004', 'Dinner'
INSERT INTO TEST '005', 'Lunch'
INSERT INTO TEST '005', 'Dinner'
SELECT
GO

I would like to get the result where one employee is only entitled to one type of meal, if employee gets free lunch, then he/she will not get free dinner anymore.
The expected result:
'001', 'Lunch'
'002', 'Lunch'
'003', 'Lunch'
'004', 'Dinner'
'005', 'Lunch'

Anyone knows how to do this?
Thanks for your kind attention.

Cheers,
Erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 22:39:16

[code]SELECT EmpID, MAX(Meal)
FROM TEST
GROUP BY EmpID
[/code]

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

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-08-17 : 00:07:16

Hi Tan,

You again. Haha. Thanks anyway.

I cannot use GROUP BY because the structure of the table is actually more than what i wrote earlier.
The actual table will look like this.

CREATE TABLE TEST
(EmpID nvarchar(10), Meal nvarchar(10), TimeFrom nvarchar(4),
TimeTo nvarchar(4), Amount decimal)
GO
INSERT INTO TEST '001', 'Lunch', '1200', '1300', 2.00
INSERT INTO TEST '001', 'Dinner', '1900', '2000', 3.40
INSERT INTO TEST '002', 'Lunch', '1200', '1300', 2.00
INSERT INTO TEST '002', 'Dinner', '1900', '2000', 3.00
INSERT INTO TEST '003', 'Lunch', '1200', '1300', 2.00
INSERT INTO TEST '004', 'Dinner', '1930', '2030', 3.00
INSERT INTO TEST '005', 'Lunch', '1200', '1300', 2.00
INSERT INTO TEST '005', 'Dinner', '1900', '2000', 3.20
SELECT
GO

And because the timing and the amount spent for meal can be different, grouping will return me all the records instead.

Expected result:
'001', 'Lunch', '1200', '1300', 2.00
'002', 'Lunch', '1200', '1300', 2.00
'003', 'Lunch', '1200', '1300', 2.00
'004', 'Dinner', '1930', '2030', 3.00
'005', 'Lunch', '1200', '1300', 2.00

... sql is fun...

ujb
Starting Member

8 Posts

Posted - 2007-08-17 : 01:03:24

I've used a table var but you get the idea, its just a mild variation on the original reply...

SET NOCOUNT ON

DECLARE @TEST TABLE
(EmpID nvarchar(10), Meal nvarchar(10), TimeFrom nvarchar(4),
TimeTo nvarchar(4), Amount decimal)

INSERT INTO @TEST VALUES ('001', 'Lunch', '1200', '1300', 2.00)
INSERT INTO @TEST VALUES ('001', 'Dinner', '1900', '2000', 3.40)
INSERT INTO @TEST VALUES ('002', 'Lunch', '1200', '1300', 2.00)
INSERT INTO @TEST VALUES ('002', 'Dinner', '1900', '2000', 3.00)
INSERT INTO @TEST VALUES ('003', 'Lunch', '1200', '1300', 2.00)
INSERT INTO @TEST VALUES ('004', 'Dinner', '1930', '2030', 3.00)
INSERT INTO @TEST VALUES ('005', 'Lunch', '1200', '1300', 2.00)
INSERT INTO @TEST VALUES ('005', 'Dinner', '1900', '2000', 3.20)

SELECT a.*
FROM
@TEST a
JOIN
(
SELECT
EmpID
,MAX(Meal) AS Meal
FROM
@TEST b
GROUP BY EmpID
) b
ON a.EmpId = b.EmpID
AND a.Meal = b.Meal
ORDER BY a.EmpID

erwine
Yak Posting Veteran

69 Posts

Posted - 2007-08-17 : 01:25:14

Hmm.. I like this idea. Thanks alot guys.

My problem is now solved. Thx.

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources