Solved

SELECT unique value on column - MS SQL (urgent)

Posted on 2009-03-30
9
3,366 Views
Last Modified: 2012-05-06
How can I get a unique TM column on this query, I need to get the unique time(tm). At this query I cannot use it by distinct.  

Sorry, this is urgent.

SELECT DISTINCT RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7) AS tm, TIME AS tme, DATE AS dte
FROM         tbl1
WHERE     (GETDATE() + 2 < DATE)
GROUP BY TIME, DATE
ORDER BY tme

  TM                   tme                         DTE
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 9:30AM            1900-01-01 09:30:00.000              2009-06-16 00:00:00.000      
10:30AM            1900-01-01 10:30:00.000              2009-06-25 00:00:00.000      
11:00AM            1900-01-01 11:00:00.000              2009-04-29 00:00:00.000      
11:15AM            1900-01-01 11:15:00.000             2009-05-12 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000             2009-04-16 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-05-08 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-06-02 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-04-20 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-05-15 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-06-12 00:00:00.000      
0
Comment
Question by:jr_bautista
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 55 total points
ID: 24023456
You're going to have to provide more definition regarding what you are trying to accomplish.  For instance, use the data that you got from your query and provide an example of the output you are trying to achieve.
If you have multiple rows with the same time value but different dates, do you want the earliest or the latest date associated with that particular time.  If you want them all, then you cannot get a DISTINCT time value because you are going to have multiple rows.
As a passing observeation, the use of Date and Time as column names is an extremely bad practice.  Those are normally Reserved Words and some SQL engines will seriously balk at their use.

SELECT Z.tm, TIME AS tme, DATE AS dte

FROM         tbl1

INNER JOIN

(

SELECT DISTINCT RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7) AS tm,

       MAX(DATE) AS DTE

FROM         tbl1

WHERE     (GETDATE() + 2 < DATE) 

GROUP BY RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7), MAX(DATE)

) Z

 ON Z. tm = RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, tbl1.TIME), 0), 7) AND

    Z.DTE = tbl1.Date

ORDER BY tme

Open in new window

0
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 70 total points
ID: 24023507
Not sure your exact problem.  If you drop DATE from the select list and from the GROUP BY you will get unique values of time for the whole result set.  If you need the DATE in there then we are missing something in the logic you are trying to apply.  Explain it a little more.
0
 

Author Comment

by:jr_bautista
ID: 24023669
We cannot drop the date column, as it needs to be there....

0
 
LVL 40

Expert Comment

by:Sharath
ID: 24023674
With your query result, what is your expected result?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:jr_bautista
ID: 24023876
This is the actual data:

DATE                        TIME
2006-10-09 00:00:00.000            1899-12-30 22:00:00.000
2006-10-09 00:00:00.000            1899-12-30 21:00:00.000
2006-10-09 00:00:00.000            1900-01-01 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 22:00:00.000
2006-10-10 00:00:00.000            1899-12-30 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 22:00:00.000


I should output this: (TM should be unique, and date should be included, never mind the tme)

  TM                   tme                         DTE
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 9:30AM            1900-01-01 09:30:00.000              2009-06-16 00:00:00.000      
10:30AM            1900-01-01 10:30:00.000              2009-06-25 00:00:00.000      
11:00AM            1900-01-01 11:00:00.000              2009-04-29 00:00:00.000      
11:15AM            1900-01-01 11:15:00.000             2009-05-12 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000             2009-04-16 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-05-08 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-06-02 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-04-20 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-05-15 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-06-12 00:00:00.000      
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24023916
I observed that there is no differecne between your query result (in original question) and the output you want (post id: 24023876).
Also, in your actual data, you don't have data for year 2009. How do you get that?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24024005
Your actual data does not support any means for extracting the following line:
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 Where in your actual data (reproduced below) does any time exist that is not a whole hour?
DATE                        TIME
2006-10-09 00:00:00.000            1899-12-30 22:00:00.000
2006-10-09 00:00:00.000            1899-12-30 21:00:00.000
2006-10-09 00:00:00.000            1900-01-01 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 22:00:00.000
2006-10-10 00:00:00.000            1899-12-30 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 22:00:00.000

Your original data had some times that were like 8:15 but the newest "actual data" that you provided does not.
Did you try the code snippet I provided?
0
 

Author Closing Comment

by:jr_bautista
ID: 31564542
8080_Diver I got error with your code, I just drop the date (sorry I messed up, it can be eliminated) .

Thank you guys, sorry it's kinda urgent and I can't entertain your question well...
0
 

Author Comment

by:jr_bautista
ID: 24024164
8080_Diver I got error with your code, I just drop the date (sorry I messed up, it can be eliminated) .

Thank you guys, sorry it's kinda urgent and I can't entertain your question well...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Full Text Search string 5 33
Recurring Excel Timelime for Veeam 2 37
Slow SQL query 12 27
sql query Help 12 34
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now