asked on

How can I select the smallest value in a row in SQL

Hello,
I am running sql 2000 and I want to avoid writing an overly complicated query. I have an audit table that contains an unique ID and then several timestamps that can be stamped in any order. I need to find the timestamp that is the smallest for each unique ID.

Table structure
ID | TimeStamp1 | TimeStamp2 | TimeStamp3 ....

I need to find the youngest timestamp in the row for each given ID. Any ideas? I have searched online and found solutions to turn the data into what I have, but I need it in the following format

ID | StampValue

Any clues?

expertsoul

A case statement should do the job:

SELECT    
CASE        
WHEN TimeStamp1 <= TimeStamp2 AND TimeStamp1 <= TimeStamp3 THEN TimeStamp1
WHEN TimeStamp2 <= TimeStamp1 AND TimeStamp2 <= TimeStamp3 THEN TimeStamp2 
WHEN TimeStamp3 <= TimeStamp1 AND TimeStamp3 <= TimeStamp2 THEN TimeStamp3
ELSE TimeStamp1 END AS 
SmallestDate

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

expertsoul

What I gave above is just a sample and It should be good for any numbers of columns.
I guess performance should be consideration on choosing the query. But I will still hang on to this one.
Just made a small modification:

SELECT  ID,  
CASE        
WHEN TimeStamp1 <= TimeStamp2 AND TimeStamp1 <= TimeStamp3 THEN TimeStamp1
WHEN TimeStamp2 <= TimeStamp1 AND TimeStamp2 <= TimeStamp3 THEN TimeStamp2 
WHEN TimeStamp3 <= TimeStamp1 AND TimeStamp3 <= TimeStamp2 THEN TimeStamp3
ELSE TimeStamp1 END AS 
StampValue
From YourTable

Open in new window

k_murli_krishna

Both queries will keep increasing in size but not complexity for more and more timestamp columns. Measure cost and time for both when there are few records and also huge number of records. Another method is transpose the data using a query and apply MIN function on transposed timetamp columns so that you will get one value for each current row from a transposed column.

If you have less then 64000 rows can you use Pivot in Excel using paste special, do a SORT ASC for each transposed column and pick the first row to get all the minimums of each row in original table.

millsap_singer

ASKER

Thank you for your assistance on this. I had come to this idea but was resisting it due to the number of columns that would need to be searched.

The reason that I like this solution over the other one using CASE statements is that you can perform other queries upon the same derived table.

Thank you.