select query

Question

select query

charipg

SSCrazy Eights

Points: 9888
More actions
January 28, 2010 at 12:59 pm

#140918

i need a select query for below.
there is 3 date colums in a table.
how to find out the greatest value from these 3 columns.

Viewing 15 posts - 1 through 15 (of 33 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Sounds like homework.

If not, please provide sample data, expected result and what you've tried so far as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

charipg SSCrazy Eights Points: 9888 More actions · Answer 2

charipg

SSCrazy Eights

Points: 9888

January 28, 2010 at 1:06 pm

#1110325

selecting one greatest value from 3 coloumns.?

charipg SSCrazy Eights Points: 9888 More actions · Answer 3

selecting one greatest value from 3 coloumns in a table.?

LutzM SSC Guru Points: 107049 More actions · Answer 4

charipg (1/28/2010)
selecting one greatest value from 3 coloumns in a table.?

Yes.

How about sample data including your current query as requested before?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

Florian Reischl

SSC-Dedicated

Points: 37301

January 28, 2010 at 1:13 pm

#1110330

Try MAX with an included CASE WHEN.

Greets

Flo

charipg SSCrazy Eights Points: 9888 More actions · Answer 6

I am not able to provide the sample data.

can you pls provide the sample query?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

charipg (1/28/2010)
I am not able to provide the sample data.
can you pls provide the sample query?

?

LutzM SSC Guru Points: 107049 More actions · Answer 8

charipg (1/28/2010)
I am not able to provide the sample data.
can you pls provide the sample query?

You don't have to provide your real data. Get the basic concept and transform it into a sample table with fake column names and fake values. But make sure it represents what you're struggling with.

Please remember we don't see what you see. So you have to help us help you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

charipg SSCrazy Eights Points: 9888 More actions · Answer 9

Please correct the below query..........

select max(myDate) from

( select field_1 myDate from myTable

union all

select field_2 myDate from myTable

union all

select field_3 myDate from myTable)

LutzM SSC Guru Points: 107049 More actions · Answer 10

charipg (1/28/2010)
Please correct the below query..........
select max(myDate) from
( select field_1 myDate from myTable
union all
select field_2 myDate from myTable
union all
select field_3 myDate from myTable)

If you add an alias at the end of your query to give your UNION statement a "pseudo table name", the query will return a result.

Note: Since you didn't mention whether you get an error message (or even what that error would have been) I'm just guessing that this is what you're looking for...

select max(myDate) from

( select field_1 myDate from myTable

union all

select field_2 myDate from myTable

union all

select field_3 myDate from myTable) uniontable

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 11

Your approach should work, doesn't it?

Maybe this could be a better performance:

SELECT

MAX(

CASE WHEN field1 > field2 THEN

CASE WHEN field1 > field3 THEN

field1

ELSE field3 END

ELSE

CASE WHEN field2 > field3 THEN

field2

ELSE field3 END

END

)

FROM myTable

Greets

Flo

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

Florian Reischl

SSC-Dedicated

Points: 37301

January 28, 2010 at 1:50 pm

#1110352

Didn't notice the missing column name(s)...

AJHall Mr or Mrs. 500 Points: 580 More actions · Answer 13

Looks like someone beat me to the punch.

Aaron Hall
IT Infrastructure Consultant

Nothing is more confounding than a DBA that uses bureaucracy as a means to inflate his power. Ever try to get an index added to a government run SQL server and you'll know what I mean.

igggis SSC Enthusiast Points: 127 More actions · Answer 14

select Max(date)

from

(select date1 from table1

union all

select date2 from table1

union all

select date3 from table1 ) as tbl