Hi.
I have a large "view" I'm working with, between 250-280 fields [not sure exactly]. Within this view there are a few fields with similar information. For example, LocationID corresponds to the Address, City, and State, but those three are seperate fields.
I also have a field with the most recent date and time that the address was used. There can be multiple records for any address if the address was used more than once in any given month.
What I need to do is pull each distinct address-if there is more than one instance of that address, the instance that was most recently touched, along with all the other 200+ corresponding fields.
I started off thinking I could sort the address ascending, and then the time descending, and just pull the first hit of each address by using the DISTINCT keyword in my query, but it keeps getting thrown off by all of the fields, and ends up not being distinct.
I was using something like this:
SELECT DISTINCT Address, BUnit, ResultDateTime
FROM dbo.vw_ReportData
WHERE BUnit='Blah'
ORDER BY Address, ResultDateTime DESC
Can anyone shed some light on this issue That where statement needs to be in there [or its effect anyhow]. Any help would be appreciated.
Thanks,
Chris
Query Question. On the right track, I think...
hallie_j2x
Hi,
ok, could you provide some more information about your table design Some sample data and expected results would be also appreciated, it is always hard to guess from the explantion the prob’lem the poster want to focus on. Thanks in advance.
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
Shanzz
did you try the MAX or MIN function (according to display the most recent / the oldest entry) in the database So you would be up with something like this:
SELECT MAX(Address), MAX(BUnit), MAX(ResultDateTime)
FROM dbo.vw_ReportData
WHERE BUnit='Blah'
Group by BUnit
I am not quite exactly sure what these columns are for, perhaps you might change them because I don’t have the background information, but thats the "one-record-per-group" solution for this.
BTW: Getting the number of columns in a view would be something like this SELECT COUNT(*) FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME ='Viewname'
HTH, Jens Suessmeyer.
---
http://www.sqlserver2005.de
---
maximus_mike
Each address can have more than one record--when it was used multiple times, so even if I used MAX or MIN on the address, it wouldn't matter.
I need to find the most recent use of each address, based off the ResultDateTime. The problem I've run into thus far is that I can't select a distinct address and have it be distinct with all of those fields added; furthermore, the sorting is thrown off. Sorting the ResultDateTime descending works until the data turns to anything in the AM, versus a PM time, the sort gets thrown off--not sure why.
If I could get those ResultDateTime sorted correctly within each address group, I'd be one step closer. But I also need some guidance in terms of keeping things distinct.
Any help