HOW To write this query?

Question

HOW To write this query?

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
June 8, 2010 at 3:15 am

#223564

There is a table named "fathers" which has two columns:FatherName and DaughterAge
Records are like this:
Father1 12
Father1 17
Father2 22
Father3 15
Father3 17
Father3 19
So father1 has 2 daughters aged 12 and 17 and Father3 has 3 & so on....
I want one row for each father showing their eldest daughter age. sth like this:
Father1 17
Father2 22
Father3 19
BUT I DONT WANT TO USE A GROUP BY in the query for some specific reason.
Any help???
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 1

Is this a homework question? We're happy to help, but please first show us what you have tried already. Also show us your table structures and give us some readily consumable test data. Have a look at Jeff Moden's article here[/url] as to how to do this.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hey there, try this :

DECLARE @Fathers TABLE (FatherId VARCHAR(10), age INT)

INSERT INTO @Fathers

SELECT 'Father1', 12

UNION ALL SELECT 'Father1', 17

UNION ALL SELECT 'Father2', 22

UNION ALL SELECT 'Father3', 15

UNION ALL SELECT 'Father3', 17

UNION ALL SELECT 'Father3', 19

;WITH CTE AS

(

SELECT ROW_NUMBER() OVER(PARTITION BY FatherId ORDER BY age DESC ) RN , * FROM @Fathers

)

SELECT * FROM CTE WHERE RN = 1

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

As this is fairly simple to construct, i myself created the sample data and scripts and the code.. in future, u can follow what Jan Van said and post data in readily consumable format, so that every one will give it a shot..

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

This should work for you.

--First things first, since you have provided this in your question,

--I build some dummy data.

DECLARE @Table1 TABLE(

[Father] VARCHAR(50),

[Oldest Daughter] INT)

INSERT INTO @Table1

VALUES ('Father1',12)

INSERT INTO @Table1

VALUES ('Father1',17)

INSERT INTO @Table1

VALUES ('Father2',22)

INSERT INTO @Table1

VALUES ('Father3',15)

INSERT INTO @Table1

VALUES ('Father3',17)

INSERT INTO @Table1

VALUES ('Father3',19)

--Now for the query

SELECT t1.*

FROM @Table1 AS t1

LEFT OUTER JOIN @Table1 AS t2

ON ( t1.[Father] = t2.[Father]

AND t1.[Oldest Daughter] < t2.[Oldest Daughter] )

WHERE t2.[Oldest Daughter] IS NULL;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 5

Why make it that complicated?

DECLARE @Fathers TABLE (FatherId VARCHAR(10), age INT)

INSERT INTO @Fathers

SELECT 'Father1', 12

UNION ALL SELECT 'Father1', 17

UNION ALL SELECT 'Father2', 22

UNION ALL SELECT 'Father3', 15

UNION ALL SELECT 'Father3', 17

UNION ALL SELECT 'Father3', 19

SELECT FatherId, MAX ( age ) AS 'age'

FROM @Fathers

GROUP BY FatherId

Or am I missing something?

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

DECLARE @Fathers TABLE (FatherId VARCHAR(10), age INT)

INSERT INTO @Fathers

SELECT 'Father1', 12

UNION ALL SELECT 'Father1', 17

UNION ALL SELECT 'Father2', 22

UNION ALL SELECT 'Father3', 15

UNION ALL SELECT 'Father3', 17

UNION ALL SELECT 'Father3', 19

-- GROUP BY disallowed (probably homework)

--SELECT FatherId, MAX ( age ) AS 'age'

--FROM @Fathers

--GROUP BY FatherId

;WITH FatherAndEldest AS (

SELECT Seq = ROW_NUMBER() OVER (PARTITION BY FatherId ORDER BY age DESC), FatherId, age

FROM @Fathers

) SELECT FatherId, age FROM FatherAndEldest WHERE Seq = 1

How will you learn unless you try?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

Jan Van der Eecken (6/8/2010)
Why make it that complicated?
DECLARE @Fathers TABLE (FatherId VARCHAR(10), age INT)
INSERT INTO @Fathers
SELECT 'Father1', 12
UNION ALL SELECT 'Father1', 17
UNION ALL SELECT 'Father2', 22
UNION ALL SELECT 'Father3', 15
UNION ALL SELECT 'Father3', 17
UNION ALL SELECT 'Father3', 19
SELECT FatherId, MAX ( age ) AS 'age'
FROM @Fathers
GROUP BY FatherId
Or am I missing something?

That was my first answer, until I noticed that for some unspecified reason he didn't want to use GROUP BY. *shrugs*

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 8

DECLARE @Fathers TABLE (FatherId VARCHAR(10), age INT)

INSERT INTO @Fathers

SELECT 'Father1', 12

UNION ALL SELECT 'Father1', 17

UNION ALL SELECT 'Father2', 22

UNION ALL SELECT 'Father3', 15

UNION ALL SELECT 'Father3', 17

UNION ALL SELECT 'Father3', 19

-- GROUP BY disallowed (probably homework)

--SELECT FatherId, MAX ( age ) AS 'age'

--FROM @Fathers

--GROUP BY FatherId

SELECT FatherID,Age FROM @Fathers F where 1 = (Select Count(1) from @fathers F1 where F.FatherID = f1.FatherID and F.age <= F1.age)

Regards,
Mitesh OSwal
+918698619998

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Jan Van der Eecken (6/8/2010)
Why make it that complicated?
SELECT FatherId, MAX ( age ) AS 'age'
FROM @Fathers
GROUP BY FatherId
Or am I missing something?

Jan Van , think u probably missed out one note from the OP

BUT I DONT WANT TO USE A GROUP BY

~Edit : Wrong quotes fixed.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 10

ColdCoffee (6/8/2010)
and post data in readily consumable format, so that every one will give it a shot..

see as i was saying this, and as there was sample data in readily consumable format, u now have 3 different answers, all producing what you want exactly 🙂 now its your turn to put all the codes over a million rows and judge the optimized one.. happy learning..

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 11

ColdCoffee (6/8/2010)
Jan Van der Eecken (6/8/2010)
Why make it that complicated?
SELECT FatherId, MAX ( age ) AS 'age'
FROM @Fathers
GROUP BY FatherId
Or am I missing something?
Jan Van , think u probably missed out one note from the OP
BUT I DONT WANT TO USE A GROUP BY
~Edit : Wrong quotes fixed.

Yeah, I misread it as "BUT I DO WANT TO USE A GROUP BY"

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 12

Thanks to all who replied this post. My appologies if it looked so trivial....

Actually it was part of a complex proc and for simplfying I used father daughter exapmple.

I was having a resultset in proc which fetches around 5.7 million records and this resultset makes use of MAX and GROUP BY and it was taking 25 secs to get this resultset. So I was just querious if there is another way to write it without GROUP BY since it was taking lot of time. Although you guys surely suggested another WAY, but it still is taking more than 20 secs.

So it again confirms the TSQL basics that you shouldn't bring huge resultsets in procs unless required.

But this proc was written by someone else and I am not so sure about business logic here. So was just wondering if there can be a faster way to pull all records from table(around 5.7 million).

Hope I make thing a bit more clear...

thanks again..

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

If you have to aggregate your results, and I'm sure you do given the number of rows you're processing, then GROUP BY is almost certainly the fastest way to do it. You might benefit more from posting the whole query, and also the plan. There are a number of factors which could apply the brakes. As an example, there's a query I was working on this morning which takes a few tens of thousands of rows from a 40-million row table, aggregates & sorts, and spits out a screenful of quite heavily processed results, all in less than three seconds.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden