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 |
|
dcobb
Yak Posting Veteran
76 Posts |
 Posted - 2002-03-12 : 05:42:14
|
| Background Info: I have one table which has three fields; log_date, log_time, log_tidl all varchars.log_tidl is duplicated and is used as an identifier, but in this case it is not unqiue (due to duplication). For example...2002-03-10,00:09:19,20020310517859432000920 2002-03-10,00:24:16,200203105178594330024162002-03-10,00:25:19,20020310517859432000920 (log_tidl duplicated)I need to pull out all log_tidl which are unique as well as the log_date & log_time. Expected output: 2002-03-10,00:09:19,20020310517859432000920 2002-03-10,00:24:16,20020310517859433002416I can use: SELECT DISTINCT log_tidl FROM table which is ok, but I cannot add the date and time fields that I require.Any Ideas?dCEdited by - dcobb on 03/12/2002 05:46:18 |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-03-12 : 07:28:20
|
| select min(log_date + ' ' + log_time), log_tidl from one_table group by log_tidlYou did not mention which value from log_date and log_time you wanted in case of duplicates. In your sample you took the lowest value, thus I used the min function. |
 |
|
|
dcobb
Yak Posting Veteran
76 Posts |
Posted - 2002-03-12 : 08:05:45
|
| The idea behind this is to try and sort out a list of unique users from our web server logs. I am assigning a unique value (log_tidl) to each user which is then duplicated on each HTTP request...So, yes I only really need the first date stamp (log_date & log_time)of log_tidl, which I am sure should be covered in LarsG's reply (nice solution).Thanks for your helpdC |
|
|
|
Scott
Posting Yak Master
145 Posts |
Posted - 2002-03-13 : 04:21:31
|
| I have a similar situation, only a different application. I have a bunch of values in a table with a Project Code field and a capture date. I want to pull all fields per project code for the last record entered for that project code. ie One project code has multiple records, I want the last record entered for each project. |
|
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-03-13 : 14:57:24
|
| select * from project as pwhere captureDate in (select max(captureDate) from project where projectCode = p.projectCode) |
|
|
|
|
|
|
|
|
SELECT DISTINCT