Bread with cheese - 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

SQL Server Development (2000)

Bread with cheese

Author

Topic

jeroena
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 09:34:03

Hi,

Imagine one day you would wake up and find the following table lying next to your bed:

C1 C2 C3
AAA 5 Jam
AAA 16 Bread
AAA 3 Milk
BBB 2 Cake
BBB 7 Cheese

Now, what if to get some bread and cheese for breakfast you would have to select C1, C2 and C3 from this table, and return only AAA en BBB with the highest C2. So the result would be:

AAA 16 Bread
BBB 7 Cheese

I came up with this (C1, C2 is uniqe):

SELECT C1, C2, C3
FROM
table
INNER JOIN
( SELECT C1, MAX(C2) as maxC2
FROM table
GROUP BY C1 ) derivedTable
ON derivedTable.C1 = table.C1 AND derivedTable.maxC2 = table.C2

This works, HOWEVER, its not very fast. Does anyone has a faster way of getting a cheese sandwich?

Thnx...
Jeroen

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-08-10 : 09:41:55

create table tmp
(c1 varchar (4),
c2 numeric,
c3 varchar(10))

insert into tmp
select 'AAA', 5, 'Jam' union
select 'AAA', 16, 'Bread' union
select 'AAA', 3, 'Milk' union
select 'BBB', 2, 'Cake' union
select 'BBB', 7, 'Cheese'

select * from tmp

select * from tmp where c2 in (
select max(c2) from tmp
group by c1)

o/p
-----------
AAA Bread 16
BBB Cheese 7

Mahesh

jeroena
Yak Posting Veteran

76 Posts

Posted - 2006-08-10 : 09:55:51

Hi,

I think this is not correct:

select * from tmp where c2 in (
select max(c2) from tmp
group by c1)

because c2 is not unique without c1.

And because I use reporting services, I'd like to use a query without temporary tables...

Jeroen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-10 : 10:37:38

You can read Mahesh's code like this:



SELECT 
	C1,C2,C3 
FROM 
	YourPermanentTableNameHere 
WHERE 
	c2 IN 
(SELECT 
	MAX(c2) 
FROM 
	YourPermanentTableNameHere
GROUP BY 
	c1
)

Looks like it works.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-10 : 16:05:21

quote:
Originally posted by jeroena

Hi,

Imagine one day you would wake up and find the following table lying next to your bed:

C1 C2 C3
AAA 5 Jam
AAA 16 Bread
AAA 3 Milk
BBB 2 Cake
BBB 7 Cheese

Now, what if to get some bread and cheese for breakfast you would have to select C1, C2 and C3 from this table, and return only AAA en BBB with the highest C2. So the result would be:

AAA 16 Bread
BBB 7 Cheese

I came up with this (C1, C2 is uniqe):

SELECT C1, C2, C3
FROM
table
INNER JOIN
( SELECT C1, MAX(C2) as maxC2
FROM table
GROUP BY C1 ) derivedTable
ON derivedTable.C1 = table.C1 AND derivedTable.maxC2 = table.C2

This works, HOWEVER, its not very fast. Does anyone has a faster way of getting a cheese sandwich?

Thnx...
Jeroen

Your method is as good as any; if it is not fast enough then you should check your indexes.

- Jeff

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-10 : 16:40:40

quote:
Originally posted by jeroena

Hi,

Imagine one day you would wake up and find the following table lying next to your bed:

C1 C2 C3
AAA 5 Jam
AAA 16 Bread
AAA 3 Milk
BBB 2 Cake
BBB 7 Cheese

Now, what if to get some bread and cheese for breakfast you would have to select C1, C2 and C3 from this table, and return only AAA en BBB with the highest C2. So the result would be:

AAA 16 Bread
BBB 7 Cheese

I came up with this (C1, C2 is uniqe):

SELECT C1, C2, C3
FROM
table
INNER JOIN
( SELECT C1, MAX(C2) as maxC2
FROM table
GROUP BY C1 ) derivedTable
ON derivedTable.C1 = table.C1 AND derivedTable.maxC2 = table.C2

This works, HOWEVER, its not very fast. Does anyone has a faster way of getting a cheese sandwich?

Thnx...
Jeroen

I have to assume that you did not post your real query, since the one you posted has obvious errors in it (reserved word "table" for a table name, column C1 is ambiguous).

If you really want help with performance issues, we would need to see your real table structure, query, some idea what the data looks like, and some idea on the amount of data in the table. The query plan would be helpful also.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources