Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Extracting Data from single table Expand / Collapse
Author
Message
Posted Monday, October 15, 2012 11:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 9:12 AM
Points: 24, Visits: 9
I am attempting to pull only open tickets from my table. Thing is we have duplicate records with different status code in our data

TICKET STATUS
9543 1
9543 5
9543 5
9544 1
9546 1
9547 1
9547 5

I only want the records with a 1 and not a 5 status, so in the above sample, I should only return 9544 and 9546.

Any suggestions are much appreciated.
Post #1372840
Posted Monday, October 15, 2012 11:28 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:52 PM
Points: 1,478, Visits: 1,020
What exactly are you trying to do here?

Can you please post better data, DDL and DML statements. You'll get more help if you follow the suggestions in this Post www.sqlservercentral.com/articles/Best+Practices.
Also Post what you have tried, and we can go from there
Post #1372848
Posted Monday, October 15, 2012 11:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 12,744, Visits: 31,071
i think a simple test with an EXISTS and correlated subquery will get you want you want:
With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
--the above is just setup data to represent your data , wherever it is.
--data like that makes it easy for anyone to offer helpful posts.
--proposed example is just what is below.
SELECT
*
FROM YOURTABLE OPENTIX
WHERE NOT EXISTS(SELECT
1
FROM YOURTABLE CLOSEDTIX
WHERE CLOSEDTIX.TICKET = OPENTIX.TICKET
AND CLOSEDTIX.STATUS = 5)
AND OPENTIX.STATUS = 1



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1372860
Posted Monday, October 15, 2012 12:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 9:12 AM
Points: 24, Visits: 9
Lowell, that was exactly what I was looking for. Thank you so much for the assistance.
Post #1372872
Posted Monday, October 15, 2012 2:11 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 8:24 AM
Points: 1,240, Visits: 5,421
I think that a GROUP BY is much simpler and probably faster, too.

With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)

SELECT TICKET, MAX(STATUS) AS STATUS
FROM YOURTABLE
GROUP BY TICKET
HAVING MAX(STATUS) = 1
GO

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #1372932
Posted Monday, October 15, 2012 3:27 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 23, 2012 9:12 AM
Points: 24, Visits: 9
Thank you for the reply. I will keep this handy.
Post #1372968
Posted Thursday, October 18, 2012 1:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
This is another way of doing it...and I guess the simplest way....

With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
Select * From YOURTABLE
Where TICKET NOT IN (Select TICKET From YOURTABLE Where STATUS = 5)
GO



Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1374175
Posted Saturday, October 27, 2012 11:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
drew.allen (10/15/2012)
I think that a GROUP BY is much simpler and probably faster, too.

With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)

SELECT TICKET, MAX(STATUS) AS STATUS
FROM YOURTABLE
GROUP BY TICKET
HAVING MAX(STATUS) = 1
GO

Drew


Oh, be careful. For this particular data, that will absolutely work. If they introduce status less than 5 that aren't cause for rejection, it won't.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377992
Posted Saturday, October 27, 2012 11:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
vinu512 (10/18/2012)
This is another way of doing it...and I guess the simplest way....

With YOURTABLE (TICKET,STATUS)
AS
(
SELECT 9543,1 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9543,5 UNION ALL
SELECT 9544,1 UNION ALL
SELECT 9546,1 UNION ALL
SELECT 9547,1 UNION ALL
SELECT 9547,5
)
Select * From YOURTABLE
Where TICKET NOT IN (Select TICKET From YOURTABLE Where STATUS = 5)
GO



It's a good idea for the data given. I have a hard time believing that they have no statuses between 1 and 5, though. If they do, the code you posted would produce duplicate ticket numbers. Of course, that may be just what the doctor ordered if there are status other than 1 and 5.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377993
Posted Saturday, October 27, 2012 11:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
dthmtlgod (10/15/2012)
I am attempting to pull only open tickets from my table. Thing is we have duplicate records with different status code in our data

TICKET STATUS
9543 1
9543 5
9543 5
9544 1
9546 1
9547 1
9547 5

I only want the records with a 1 and not a 5 status, so in the above sample, I should only return 9544 and 9546.

Any suggestions are much appreciated.


The question that begs to be answered here is.... in real life, will there be any tickets with a status other than 1 and 5 and what do you want to do if that's true?


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1377994
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse