How would I Find closest match......

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)

How would I Find closest match......

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-05-27 : 10:01:36

I have an access database I'm using as part of a .NET windows form I'm creating.

In this application, someone will enter a number like "103" and I need to pull the row where a certain column ("Flowrate") is the closest match to that entered value.

So psuedo-sql would be,

select * from dryer_data where flowrate is closest = 103;

The column "flowrate" is a Number datatype.

Any ideas?

JimiC
Starting Member

29 Posts

Posted - 2003-05-27 : 10:32:48

just looking at your field names .. if this is to select a model with the specification fitting the users need, could you just select the first register with flowrate greater than or equal to the specified user needs ?

in that case the psuedo sql being

select top 1 * from dryer_data
where flowrate >= 103
order by flowrate asc

if you really need to get the closest (above or below) id be tempted to call another select to get the flowrate lower than that specified by the user, and evaluate which is closest in your code.

but then, i am a bit hard of thinking today, no doubt someone has a nicer answer :)

----------------------
Making things work by dint of dinting.

JCamburn
Starting Member

31 Posts

Posted - 2003-05-27 : 10:38:33

The following assumes that ID is your primary key. If more than one row would match, this query will return the row with the "closest" flowrate and the lowest ID. Hope this helps.


SELECT TOP 1 
    dryer_data.* 
FROM 
    dryer_data 
    INNER JOIN
    (
        SELECT 
            dryer_data.ID, 
            Min(Abs(103-flowrate)) AS Difference
        FROM 
            dryer_data
        GROUP BY 
            dryer_data.ID
    ) AS Q1
    ON dryer_data.ID = Q1.ID
    ORDER BY 
        dryer_data.ID;

label
Posting Yak Master

197 Posts

Posted - 2003-05-27 : 12:43:37

quote:

The following assumes that ID is your primary key. If more than one row would match, this query will return the row with the "closest" flowrate and the lowest ID. Hope this helps.


SELECT TOP 1 
    dryer_data.* 
FROM 
    dryer_data 
    INNER JOIN
    (
        SELECT 
            dryer_data.ID, 
            Min(Abs(103-flowrate)) AS Difference
        FROM 
            dryer_data
        GROUP BY 
            dryer_data.ID
    ) AS Q1
    ON dryer_data.ID = Q1.ID
    ORDER BY 
        dryer_data.ID;

Nice, I like that approach.

Thanks for both of your inputs...much appreciated.

Peter.Bij@klm.com
Starting Member

12 Posts

Posted - 2003-05-28 : 04:42:22

To see the difference, just add the Q1.Difference to the primary Select statement, like this:

SELECT TOP 1
dryer_data.* , q1.Difference
FROM
dryer_data
INNER JOIN
(
SELECT
dryer_data.ID,
Min(Abs(103-flowrate)) AS Difference
FROM
dryer_data
GROUP BY
dryer_data.ID
) AS Q1
ON dryer_data.ID = Q1.ID
ORDER BY
dryer_data.ID;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources