Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
 Posted - 2009-02-26 : 16:25:27
|
I really can't think of a better Subject than the above. Let me get right into it.I have a table (let's call it Residence) and it has columns of ID (identity), Name, Address, and LatestRecordAn example might look like this in the table:ID Name Address LatestRecord456 John Jones 123 Some Street 0459 John Jones, Sr. 123 Some Street 1245 James Andrews 456 Some Street 0394 James Andrews 457 That Street 1495 Jim Andrews 457 That Street 2 I need a select query which selects only the rows which have the LatestRecord. Like, perhaps one can see the resulting set of rows I want; just in case, here is what it should look like:ID Name Address LatestRecord459 John Jones, Sr. 123 Some Street 1495 Jim Andrews 457 That Street 2 Now I can take the Residence table and inner join it with itself on the max ID and max LatestRecord. But I figure there has to be better, more simpler way. I have other tables like this with a LatestRecord column and it can get real messy if I have to join 3 tables or more! So, I'm asking if there's a way to efficiently select only the most updated Rows.Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2009-02-27 : 09:33:18
|
I had an alternative to the inner join. With permission (cause these are not my tables), I added an int column that can be used as a type-of Row Number for. So for example, the table now looks like this:ID Name Address LatestRecord Address_Enumerator456 John Jones 123 Some Street 0 1459 John Jones, Sr. 123 Some Street 1 1245 James Andrews 456 Some Street 0 2394 James Andrews 457 That Street 1 2495 Jim Andrews 457 That Street 2 2 See, so now, I have a number that essentially enumerates through each unique name and address pair. Obviously, the code that inserts this code has to have some intelligence to know if it's modifying a person's name and/or address or if it's a brand new name & address pair. In any event, yes, the inner join solution works. But I worry about 3 or more tables. With 3 tables and an inner join per table to itself, that's 6 tables joined in all! I had another method that uses the MAX function. It looks less crude and elegant than the inner join. Performance-wise, I"m not sure. Which one is typically better for performance, JOIN or MAX?This is what my query looks like with MAX:SELECT MAX(Address_Enumerator), Address_Enumerator MAX(Name), Name MAX(Address), Address MAX(ID), Max_ID MAX(LatestRecord) Max_LatestRecordFrom Residence RGroup By LatestRecordAnd it worked! Again, this avoids the use of inner joins and I think it looks crude. But I don't know if there are other ways in MSSQL 2000 or if this is a larger performance hit than inner joining Residence with itself and retrieving the rowset I desire in that fashion. I'd be most grateful if one would inquire about the performance gains/drawbacks with these two methods. Or, if another solution opened up because of new column added to the table.Thank you very much! |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-27 : 09:40:05
|
[code]SELECT t1.ID, t1.Name, t1.Address, t1.LatestRecordFROM Residence AS t1INNER JOIN ( SELECT ID, MAX(LatestRecord) AS LatestRecord FROM Residence WHERE LatestRecord > 0 GROUP BY ID ) AS t2 ON t2.ID = t1.IDWHERE t1.LatestRecord = t2.LatestRecord[/code]
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Need help formulating a select query