Not the greatest value - 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

Not the greatest value

Author

Topic

emurphy4
Starting Member

17 Posts

Posted - 2009-11-19 : 15:45:37

I still need help with my other post, but here's another issue that I'm having.
Below is the query that I'm using. In this case, I have a unique value to use as the key, but I need to pull all records except the one with the greatest value and I'm not sure of the syntax. The phase "NOT THE GREATEST" is where I need help.

select a.*
from dbo.ncpcp_pharmacy_encounters a, dbo.ncpcp_pharmacy_encounters b
where a.script_id = b.script_id
and a.days_supply_nbr = b.days_supply_nbr
and a.dispensed_qty_nbr = b.dispensed_qty_nbr
and a.ncpdp_clm_desc = b.ncpdp_clm_desc
and a.ndc_cd = b.ndc_cd
and a.provider_id = b.provider_id
and a.script_dt = b.script_dt
and a.service_dt = b.service_dt
and a.mco_batch_id = b.mco_batch_id
and a.tran_type_cd <> b.tran_type_cd
and a.system_timestamp = b.system_timestamp
and a.mco_icn "NOT THE GREATEST"
order by a. script_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 00:46:45

can you post some sample data from table and then specify what output you're looking at?

peterkirubakaran
Starting Member

12 Posts

Posted - 2009-11-22 : 11:09:04

hi

if you only need "NOT THE GREATEST" to be done, I don't think you need to do self join...
instead write a subquery like
mco_inc NOT IN(SELECT MAX(colname) from ncpcp_pharmacy_encounters)

Peets

emurphy4
Starting Member

17 Posts

Posted - 2009-11-23 : 15:51:09

Here's the sample data(I can't seems to keep the formatting when pasting). When I add and "a.mco_icn < b.mco_icn" it work when the number of matching rows is odd and an even number of matching rows were the column tran_type_cd isn't the same. That is correct, but it doesn't work for the other scenarios. Basically, I to select(then delete after verifying) all records with the matching criteria from my query were the column tran_type_cd has "B1" in one row and "B2" in another row. So if there are 4 matching rows with 1 containing "B2" and 3 containing "B1", I need to be able to select/delete the row with "B2" and one of the rows with "B1". From the sample data below I would need to select/delete rows 4-7 and even through rows 8-9 match, they both have "B1" for tran_type_cd and should stay.

sender_id mco_batch_id mco_icn mco_batch_dt mco_batch_tm ncpdp_clm_desc tran_type_cd ndc_cd service_dt script_id days_supply_nbr script_dt dispensed_qty_nbr recipient_id provider_qualifier_id provider_id prescriber_qualifier_id prescriber_id UCC_amt system_timestamp data_file_name amount
78432 2009297 275817401 10/24/2009 1221 PHARMACY B1 4024451 5/4/2009 550465 30 3/3/2009 1200 17495776161 1 1205849980 104699 00:41.1 pharm_78432_102809.txt
78429 2009297 495194601 10/24/2009 1647 PHARMACY B1 93316006 8/24/2009 550465 10 8/24/2009 2000 76341230651 1 1888364466 10799 01:59.2 pharm_78429_102809.txt
78429 2009297 632224001 10/24/2009 1647 PHARMACY B1 168013315 8/27/2009 550465 5 8/27/2009 1500 94711674263 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632246901 10/24/2009 1647 PHARMACY B2 168013315 8/27/2009 550465 5 8/27/2009 1500 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632412901 10/24/2009 1647 PHARMACY B1 168013315 8/27/2009 550465 5 8/27/2009 1500 94716174263 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632486001 10/24/2009 1647 PHARMACY B2 168013315 8/27/2009 550465 5 8/27/2009 1500 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632604801 10/24/2009 1647 PHARMACY B1 168013315 8/27/2009 550465 5 8/27/2009 1500 94711674263 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632748901 10/24/2009 1647 PHARMACY B1 168013315 8/27/2009 550465 5 8/27/2009 1500 1 9995740488 2399 01:59.2 pharm_78429_102809.txt
78429 2009297 632749701 10/24/2009 1647 PHARMACY B1 168013330 8/27/2009 550465 14 8/27/2009 3000 94716742263 1 9995740488 3599 01:59.2 pharm_78429_102809.txt
78432 2009297 2744497301 10/24/2009 1221 PHARMACY B1 4024451 5/30/2009 550465 30 3/3/2009 1200 74957761631 1 7655843380 104699 00:41.1 pharm_78432_102809.txt
78429 2009297 6360698001 10/24/2009 1357 PHARMACY B1 59310017780 6/24/2009 550465 30 6/9/2009 7300 82367343636 1 9999489746 11199 01:55.7 pharm_78429_102809.txt
78432 2009297 7019148501 10/24/2009 914 PHARMACY B1 4024451 3/3/2009 550465 30 3/3/2009 1200 74395776161 1 1205999380 104699 00:30.2 pharm_78432_102809.txt
78432 2009297 8742298801 10/24/2009 1055 PHARMACY B1 4024451 4/3/2009 550465 30 3/3/2009 1200 7.49578E+11 1 9905843389 104699 00:31.8 pharm_78432_102809.txt

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-11-24 : 16:25:38

quote:
Originally posted by emurphy4

The phase "NOT THE GREATEST" is where I need help.

and a.mco_icn <> "muhammad ali"

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources