help with sql query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with sql query

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2015-03-20 : 17:31:58

Hello all,

I have a table in which the columns has values like this


col1

Master Card

6.000000987 PQ01

6.000000987 PQ02
6.000000987 PQ03


6.000000345 PQ03

6.000000345 PQ04

Test Card2

P00011-04-8765
P00011-05-9876
P00011-05-9876
P00011-09-1234
P00011-10-3333
P00011-10-2222
P00011-09-1111
P00011-10-2345
P00011-11-4532
P00011-12-2345
P00011-13-4532
P00011-14-2345
P00011-15-4532

Visa Card

In the above table, I need to write the query to get all the P00011-04-8765 that has a mid number higher than 10 so I only need P00011-11-4532,P00011-12-2345,P00011-13-4532,P00011-14-2345,P00011-15-4532. In these the mid number is 11, 12,13, 14 and 15

Mid number is substring([col] , 8, 2), I also need all the text values like master card, test Card2 and Visa Card and I also need, the top most row of the repeated values so for e.g, I have 6.000000987 PQ01,

6.000000987 PQ02, I only need the 6.000000987 PQ01 and 6.000000345 PQ03 and not 6.000000345 PQ02,6.000000987 PQ03 and 6.000000345 PQ04, the repeated values are the once that has same ending number before the space so 6.000000987 PQ01,

6.000000987 PQ02 are repeated values and 6.000000345 PQ03, 6.000000345 PQ04 are repeated values. I only need the top row of the repeated values.

The final table will be:


Col1


Master Card
6.000000987 PQ01
6.000000345 PQ03
Test Card2
P00011-11-4532
P00011-12-2345
P00011-13-4532
P00011-14-2345
P00011-15-4532

any help will be appreciated.

jleitao
Posting Yak Master

100 Posts

Posted - 2015-03-20 : 18:31:36

not sure if everything "result" with your data.

However try this:

PS - i have created 3 distinct query. One for each "scenario" you need.

-- #1 --

select col1
FROM TAB
where SUBSTRING(col1, 7,4) like '-%-'
and SUBSTRING(col1, 8,2) > 10

UNION

-- #2 --
select
col1
from TAB
where ISNUMERIC( substring(col1,3,1)) = 0 -- check if the 3rd char is not numeric. confirm if this is a valid condition to your data

UNION

-- #3 --
SELECT
COL1_AUX1 + ' ' + MIN(COL1_AUX2)
FROM(
SELECT
col1,
LEFT(col1, CHARINDEX(' ', COL1, 1) -1) AS COL1_AUX1,
RIGHT(col1, LEN(col1) - CHARINDEX(' ', col1, 1)) COL1_AUX2
FROM TAB
WHERE RIGHT(col1, 4) LIKE 'PQ%' -- confirm if this is a valid condition to your data
)Z
GROUP BY COL1_AUX1
having COUNT(*) >1

------------------------
PS - Sorry my bad english

anjali5
Posting Yak Master

121 Posts

Posted - 2015-03-23 : 16:59:08

quote:
Originally posted by jleitao

not sure if everything "result" with your data.

However try this:

PS - i have created 3 distinct query. One for each "scenario" you need.

-- #1 --

select col1
FROM TAB
where SUBSTRING(col1, 7,4) like '-%-'
and SUBSTRING(col1, 8,2) > 10

UNION

-- #2 --
select
col1
from TAB
where ISNUMERIC( substring(col1,3,1)) = 0 -- check if the 3rd char is not numeric. confirm if this is a valid condition to your data

UNION

-- #3 --
SELECT
COL1_AUX1 + ' ' + MIN(COL1_AUX2)
FROM(
SELECT
col1,
LEFT(col1, CHARINDEX(' ', COL1, 1) -1) AS COL1_AUX1,
RIGHT(col1, LEN(col1) - CHARINDEX(' ', col1, 1)) COL1_AUX2
FROM TAB
WHERE RIGHT(col1, 4) LIKE 'PQ%' -- confirm if this is a valid condition to your data
)Z
GROUP BY COL1_AUX1
having COUNT(*) >1

------------------------
PS - Sorry my bad english

Thank You.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources