use of Group by
Hello everyone, I have the following issue:
Having the next database diagram:
I need to get the max birth_date grouping by hobby. I also need the other fields of the same row that came with the aggregate function MAX
SELECT HOBBY ,MAX(BIRTH_NAME) FROM PEOPLE GROUP BY HOBBY
this will give me the hobby and the dates of birth. Now how can I get the other fields of these selected rows?
something like:
SELECT HOBBY ,MAX(BIRTH_NAME), ID, NAME, LAST_NAME FROM PEOPLE GROUP BY HOBBY
here you can download the create table + inserts to try this in your home or work
CREATE-TABLES.sql
INSERTS.sql
in advance, many thanks
Having the next database diagram:
I need to get the max birth_date grouping by hobby. I also need the other fields of the same row that came with the aggregate function MAX
SELECT HOBBY ,MAX(BIRTH_NAME) FROM PEOPLE GROUP BY HOBBY
this will give me the hobby and the dates of birth. Now how can I get the other fields of these selected rows?
something like:
SELECT HOBBY ,MAX(BIRTH_NAME), ID, NAME, LAST_NAME FROM PEOPLE GROUP BY HOBBY
here you can download the create table + inserts to try this in your home or work
CREATE-TABLES.sql
INSERTS.sql
in advance, many thanks
anillucky31
SELECT HOBBY ,MAX(BIRTH_Date), MAX(ID), MAX(NAME), MAX(LASTNAME) FROM PEOPLE GROUP BY HOBBY
use this
SELECT HOBBY ,MAX(BIRTH_NAME), ID, NAME, LAST_NAME FROM PEOPLE
GROUP BY HOBBY, ID, NAME, LAST_NAME
SELECT HOBBY ,MAX(BIRTH_NAME), ID, NAME, LAST_NAME FROM PEOPLE
GROUP BY HOBBY, ID, NAME, LAST_NAME
You can use a sub query
SELECT
HOBBY ,
(SELECT MAX(BIRTH_NAME) FROM PEOPLE WHERE ID = p.ID),
ID,
NAME,
LAST_NAME
FROM PEOPLE p
OR other way around
SELECT
HOBBY ,
MAX(BIRTH_NAME) ,
(SELECT TOP 1 ID FROM PEOPLE WHERE ID = p.HOBBY),
(SELECT TOP 1 NAME FROM PEOPLE WHERE ID = p.HOBBY),
(SELECT TOP 1 LAST_NAME FROM PEOPLE WHERE ID = p.HOBBY)
FROM PEOPLE p GROUP BY HOBBY
SELECT
HOBBY ,
(SELECT MAX(BIRTH_NAME) FROM PEOPLE WHERE ID = p.ID),
ID,
NAME,
LAST_NAME
FROM PEOPLE p
OR other way around
SELECT
HOBBY ,
MAX(BIRTH_NAME) ,
(SELECT TOP 1 ID FROM PEOPLE WHERE ID = p.HOBBY),
(SELECT TOP 1 NAME FROM PEOPLE WHERE ID = p.HOBBY),
(SELECT TOP 1 LAST_NAME FROM PEOPLE WHERE ID = p.HOBBY)
FROM PEOPLE p GROUP BY HOBBY
ASKER
Thank you both for the fast reply :)
I tried both queries with no luck
@anillucky31:
I thought it was ok, but then I tried executing a validating query and as you can see, it returns garbage
@sureshbabukrish:
I only have 3 Hobbies, how this gives me so many rows?
Any other idea?
I tried both queries with no luck
@anillucky31:
I thought it was ok, but then I tried executing a validating query and as you can see, it returns garbage
@sureshbabukrish:
I only have 3 Hobbies, how this gives me so many rows?
Any other idea?
ASKER
Thank you for answering kaminda,
here is the resultset with no luck :(
I've heard that you can use PARTITION statement to achieve this, but I don't have a clue on how use it
here is the resultset with no luck :(
I've heard that you can use PARTITION statement to achieve this, but I don't have a clue on how use it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for ur answer anillucky31, this worked perfect :)
here the resultset
here the resultset
ASKER
Thank you :)