Is it really a complex query?

Question

Is it really a complex query?

Usman Tanveer

SSC Journeyman

Points: 94
More actions
February 25, 2005 at 11:16 pm

#65993

Hey there...
Consider the following table having three fields.
        TimeIn                    PointValue    PointId
  2/11/2005 9:00:00 AM         111            1
  2/11/2005 9:01:00 AM         222            1
  2/11/2005 9:02:00 AM         333            1
2/11/2005 9:00:00 AM         444            2
  2/11/2005 9:01:00 AM         555            2
Now the problem is i want to select the "PointValue" against the max "TimeIn" for each "PointId".
i.e
I want the following result from the above table
  2/11/2005 9:02:00 AM         333            1
  2/11/2005 9:01:00 AM         555            2
Can some buddy tell me the query to get this result set.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Charles Otten Default port Points: 1475 More actions · Answer 1

You have 1 question and 1 issue. You don't have a key field in this table, unless it is a combination of the pointid and timein. Which is using a datetime as part of the key identifier (always a pain in the ***). There are two solutions. The first one requies a table change, but is the most simple solution.

1. Add an identity field (PointIDKey) to the table that is an auto increment. The use the following SQL.

assuming the tablename is tPoints

select * from tpoints tp where PointIDKey = (select top 1 pointidkey from tpoints where pointid = tp.pointid order by timein desc)

2. Run the following SQL

select * from tpoints tp where cast(timein as varchar(20)) + 'ID' + cast(pointid as varchar) = (select top 1 cast(timein as varchar(20)) + 'ID' + cast(pointid as varchar) from tpoints where pointid = tp.pointid order by timein desc)

If the phone doesn't ring...It's me.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Usman,

Charles is right, an IDENTITY column might help to guarantee row uniqueness is this case. However, whether you have an IDENTITY column or not (sometimes can't change a table because of 3rd party requirements), the following will work nicely... (I'd actually do it this way even if an IDENTITY column were present )

First, according to the rules of "divide and conquer", you actually have two problems defined...

Find the max TimeIn for each PointID
Display the entire row for each item returned in (1) above

The following code solves the first part of the problem...

SELECT PointID,

MAX(TimeIn) AS MaxTimeIn

FROM YourTable

GROUP BY PointID

It returns the following..

PointID MaxTimeIn

----------- ------------------------------------------------------

1 2005-02-11 09:02:00.000

2 2005-02-11 09:01:00.000

We couldn't add the PointValue column to the above code because it would mess up the grouping on PointID. Part 2 of the problem says need to return the rest of the columns in the rows associated with the above returns... it would be really nice if we could join the results of the above code back to the table so we could display the PointValue. And, actually, it's pretty easy... the following code uses the results from the above code as if it were a table. It's called a "derived table"...

SELECT y.TimeIn,

y.PointValue,

y.PointID

FROM YourTable y,

(--Derived table finds max TimeIn for each PointID

SELECT PointID,

MAX(TimeIn) AS MaxTimeIn

FROM YourTable

GROUP BY PointID

) d --End of derived table aliased as "d"

WHERE y.TimeIn = d.MaxTimeIn

AND y.PointID = d.PointID

ORDER BY y.PointID

The way you make a derived table is simple... enclose an already tested SELECT statement in parenthesis, give it an ALIAS ("d" was used in this case) as you would any table, put it in the FROM clause of another SELECT, and reference it and the columns it creates just as if it were a table.

Using the data you posted and the above code (you'll need to replace "YourTable" with the actual name of your table), you'll get the following return...

TimeIn PointValue PointID

------------------------------------------------------ ----------- -----------

2005-02-11 09:02:00.000 333 1

2005-02-11 09:01:00.000 555 2

(2 row(s) affected)

Lemme know if this helped you, eh?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Usman Tanveer SSC Journeyman Points: 94 More actions · Answer 3

Thank you both so much.....Well after i thought i wrote the same code as given by Jeff But any ways thanks alot for the detailed reply.

One more thing.....there is no primary key in the table because there are abt 1000 Points whose values are coming after about every second. Also this table will always be joined on the basis of PointId, so according to my little knowledge auto-incremented primary key was not of any use.

If i am wrong please let me know...

Thanks

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

>"1000 Points whose values are coming after about every second"

Does this mean that the table grows by a thousand rows every second? If so, some well designed indexes would help. A compound Primary Key starting with the date column may be of some use in speeding up your queries on what will rapidly become a very large table if allowed to run very long.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)