Prev: Browse - How to Remove Duplicate Items
Next: splitting of data elements - help in best way forward
From: eliza on 15 Jun 2010 03:16
If we are using SELECT TOP N query, then it returns exactly N records, and drops any record arbitrarily that has the same value as the last record in the result set.

Suppose we want a report showing top 50 costly items. There may be a situation in which the price of 50th item is same as one or more items down the list. At that point how can we have a better solution? Do we want to include other products that are tied for the same prices as the item in the 50th position?

There is an easy way to solve the problem caused by tied values in the last position of your top list arbitrarily capping the results. And it the SQL clause "With TIES"

http://www.mindfiresolutions.com/Using-WITH-TIES-SQL-Clause-936.php



Tem wrote:

get closest date
30-May-08

select * from table1 where date = '1/1/2008 12:00:00 AM' order by date asc

I would like it to query not only rows that match the exact date but also
ones that are close if no rows of the extract is returned.

Thank you
Tem

Previous Posts In This Thread:

On Friday, May 30, 2008 10:15 PM
Tem wrote:

get closest date
select * from table1 where date = '1/1/2008 12:00:00 AM' order by date asc

I would like it to query not only rows that match the exact date but also
ones that are close if no rows of the extract is returned.

Thank you
Tem

On Friday, May 30, 2008 10:59 PM
Plamen Ratchev wrote:

Re: get closest date
Here is one way:

SELECT TOP(1) WITH TIES <columns>
FROM Foo
ORDER BY ABS(DATEDIFF(DAY, date_column, '20080101'));

Based on the values of your dates (if they have time different than
midnight) you can change DATEDIFF to calculate difference in minutes,
seconds, etc.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Saturday, May 31, 2008 5:20 AM
Eric Isaacs wrote:

Not sure if this is exactly what you're looking for, but you could dosomething
Not sure if this is exactly what you're looking for, but you could do
something like this...

DECLARE @SearchDate DATETIME

SET @SearchDate = '1/1/2008 12:00:00 AM'

IF EXISTS ( SELECT 1 FROM table1 WHERE date = @SearchDate )
BEGIN
SELECT
1
FROM
table1
WHERE
date = '1/1/2008 12:00:00 AM'
ORDER BY
date ASC
END

ELSE
BEGIN
SELECT
*
FROM
table1
WHERE
date BETWEEN DATEADD(minute, -30, @SearchDate) AND
DATEADD(minute, 30, @SearchDate)
ORDER BY
date ASC
END

On Saturday, May 31, 2008 5:20 AM
Eric Isaacs wrote:

Plamen,I like it! Very cool use of the WITH TIES option as well!
Plamen,


I like it! Very cool use of the WITH TIES option as well!

Tem,

For your reference:

WITH TIES
Specifies that additional rows be returned from the base result set
with the same value in the ORDER BY columns appearing as the last of
the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an
ORDER BY clause is specified.

On Saturday, May 31, 2008 8:27 AM
Gert-Jan Strik wrote:

Yet another solution.
Yet another solution.

This will be faster if you have many rows (provided you have "date"
indexed).

SELECT TOP 1 *
FROM (
SELECT TOP 1 *, DATEDIFF(second, "date", '1/1/2008 12:00:00 AM') AS
time_difference
FROM table1
WHERE "date" <= '1/1/2008 12:00:00 AM'
ORDER BY "date" DESC
UNION ALL
SELECT TOP 1 *, DATEDIFF(second, '1/1/2008 12:00:00 AM', "date") AS
time_difference
FROM table1
WHERE "date" > '1/1/2008 12:00:00 AM'
ORDER BY "date" ASC
) T
ORDER BY time_difference

--
Gert-Jan
SQL Server MVP


Tem wrote:

On Saturday, May 31, 2008 6:20 PM
Tem wrote:

Do you guys have any recommendation on books or websites about creating
Do you guys have any recommendation on books or websites about creating
indexes?


Submitted via EggHeadCafe - Software Developer Portal of Choice
MSChart For VB.Net
http://www.eggheadcafe.com/tutorials/aspnet/45729dc8-c102-4dc6-9aa7-4f6246763650/mschart-for-vbnet.aspx
 | 
Pages: 1
Prev: Browse - How to Remove Duplicate Items
Next: splitting of data elements - help in best way forward