problem with duplicat select values

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

SQL Server 2000 Forums

SQL Server Development (2000)

problem with duplicat select values

Author

Topic

tmarko
Starting Member

15 Posts

Posted - 2007-10-06 : 13:45:46

I want to only select one row for each

CONSENSUS_RECOMMENDATIONS.ORIGDATE. So if the

column CONSENSUS_RECOMMENDATIONS.ORIGDATE consists of several rows with

the same date (despite different time), the Maximum date in column

CONSENSUS_RECOMMENDATIONS.EXPIRDATE decide which value to select.

The problem is when a CONSENSUS_RECOMMENDATIONS.EXPIRDATE has

for example 2 dates with exact the same date (date+time), I receive 2 rows as well

My subquery using MAX function must deliver ONE row but the outer clause will then

select the 2 rows I guess, so how can I eliminte the duplicate rows?

USE dB1
select
distinct COM.COM_id,

5-CR.MEAN,

CR.ORIGDATE

from dB2.dbo.CONSENSUS_RECOMMENDATIONS CR
JOIN dB2.dbo.COMPANY_INFO CI
ON CR.COMPANY_ID=CI.COMPANY_ID
JOIN dB1.dbo.CUR_Currency CUR
ON CI.CURRENCY_CODE=CUR.CUR_code
JOIN dB1.dbo.COO_CompanyCompanySource COO
ON CI.ISIN=COO.COO_sourceCode
JOIN dB1.dbo.COO_CompanyCompanySource COO1
ON CI.RIC=COO1.COO_sourceCode
JOIN dB1.dbo.COM_Company COM ON
COO.COO_COM_id=COM.COm_id
JOIN COD_Codes COD on
CR.MEAN_LABEL = COD.COD_name

where
(CR.EXPIRDATE) IN

(select MAX(dB2.dbo.CONSENSUS_RECOMMENDATIONS.EXPIRDATE)
from dB2.dbo.CONSENSUS_RECOMMENDATIONS
where CR.COMPANY_ID=dB2.dbo.CONSENSUS_RECOMMENDATIONS.COMPANY_ID
and CONVERT(VARCHAR(30),CR.ORIGDATE,103)=
CONVERT(VARCHAR(30),dB2.dbo.CONSENSUS_RECOMMENDATIONS.ORIGDATE,103)
)
and COM.COM_id=217
and CUR.CUR_id=COM.COM_CUR_priceCurrencyId
group by CR.EXPIRDATE, CR.ORIGDATE,COM.COM_id, CR.MEAN
order by COM.COM_id,CR.ORIGDATE

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 14:16:59

Instead of MAX use this?


SELECT TOP 1 dB2.dbo.CONSENSUS_RECOMMENDATIONS.EXPIRDATE
from dB2.dbo.CONSENSUS_RECOMMENDATIONS
where CR.COMPANY_ID=dB2.dbo.CONSENSUS_RECOMMENDATIONS.COMPANY_ID
and CONVERT(VARCHAR(30),CR.ORIGDATE,103)=
CONVERT(VARCHAR(30),dB2.dbo.CONSENSUS_RECOMMENDATIONS.ORIGDATE,103)
ORDER BY dB2.dbo.CONSENSUS_RECOMMENDATIONS.EXPIRDATE DESC,
dB2.dbo.CONSENSUS_RECOMMENDATIONS.PKCol1, dB2.dbo.CONSENSUS_RECOMMENDATIONS.PKCol2, ...

Please note that converting the dates to VARCHAR to compare them is very inefficient.

Kristen

tmarko
Starting Member

15 Posts

Posted - 2007-10-06 : 15:15:19

Hi Kristen

No difference, the query still deliver 2 rows with the same date.

I guess the subquery deliver a single value but the

where CR.EXPIRDATE IN

will deliver 2 values because CR have 2 rows matching the single row from the subquery or do I miss something?

What should I do instead of converting the dates to VARCHAR?

I want to check if its the same ORIGDATE based on date not the whole datetime.

I thought the only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type

regards

Kristen
Test

22859 Posts

Posted - 2007-10-07 : 02:38:25

Doh! Yes, sorry, of course MAX() can only deliver one row ...

You'll have to use SELECT TOP 1 on a Sub Select to get the CR details for one specific record that matches the MAX(EXPIRDATE)

"What should I do instead of converting the dates to VARCHAR?"

You can use

DATEADD(Day, DATEDIFF(Day, 0, MyCol), 0)

to "round" MyCol to date only, with time being 00:00:00.

That is faster because it does not require a datatype conversion. It looks a bit technical though!

However, both methods will prevent a suitable index on that column (if there is one) being used for the query. If this is a common requirement you might want to have an additional column to store the "date part" only, and then index that.

Kristen

tmarko
Starting Member

15 Posts

Posted - 2007-10-07 : 12:25:40

Hi again!

I don't think I really understand what you are writing beacuse I still do not get what I want when running the query, besides the dateadd thing.

Even when testing the following code

select
COM.COM_id,
5-CR.MEAN,
MAX(CR.EXPIRDATE)

I get 2 exact the rows for CE.EXPIRDATE (same date+time)

strange!

Kristen
Test

22859 Posts

Posted - 2007-10-07 : 13:46:28

Use something like this:


JOIN
(
    SELECT TOP 1 PkColumn
    FROM dB2.dbo.CONSENSUS_RECOMMENDATIONS
    WHERE EXPIRDATE =
    (
        select MAX(dB2.dbo.CONSENSUS_RECOMMENDATIONS.EXPIRDATE) 
        from   dB2.dbo.CONSENSUS_RECOMMENDATIONS
        where      CR.COMPANY_ID=dB2.dbo.CONSENSUS_RECOMMENDATIONS.COMPANY_ID
               and CONVERT(VARCHAR(30),CR.ORIGDATE,103)=
                   CONVERT(VARCHAR(30),dB2.dbo.CONSENSUS_RECOMMENDATIONS.ORIGDATE,103)
    )
    ORDER BY PkColumn
) AS X
    ON X.PkColumn = CR.PkColumn

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources