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
Last Comment
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you for ur answer anillucky31, this worked perfect :)
here the resultset
here the resultset
ASKER
Thank you :)
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
