Get Table Rows Priority Wise

I have a table.

Highlight
----

Id

Name

Detail

StartDate

EndDate

Priority

I want to make a query which returns 1 Highlight in the current date.

But remember I have already set the Hightlight Priority 1 to 5. And I want that Hight Priority rows select more times than Low Priority Rows.

Not sure I understand the question, but to return a single row, you can use TOP 1 and for your priority, use an ORDER BY. So for example:

SELECT TOP 1 * FROM [Hightlight] WHERE [StartDate] = GETDATE() ORDER BY [Priority]

-Damien

|||

I need that.

Every time I select top 1 row different Highlight will select.

But the hightlight which has high priority select more times then low priority.

|||

Are you trying to get a random result? Basically the statement I provided will only ever return 1 result with the highest priority.

Not sure I understand what you are trying to do...

-Damien

|||

I think this is waht you need:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4ORDER BY [Priority],[StartDate], [EndDate]

I suggest you to have another column (to act as a flag) for the FinishedTasks (IsFinished or IsDone column).

So, you can list the top 1 high priority that assign to you first and not finished yet and it has the closest deadline (EndDate), by using the following example:

1SELECT TOP 1 *2FROM [Hightlight]3WHERE [StartDate] =Convert(varchar(20),GETDATE(),111)4AND [IsFinished] = 1-- (0:Finished and 1:Not Finished)5ORDER BY [Priority],[EndDate]6

Good luck.|||

But I don't want this.

I want that row will select randomly and the row which has high priority shows many time as compare to low priority hightlight.

But in your prescribed condition High Priority row will select till it expires and other will wait to expire.

|||

Hi,

If you want to show random values, you can try Order By NewID() .

Try the following codes (I suppose Max(Priority) will have the most priority. Or you can use MIN() )

1 SELECT TOP 1 *
2 FROM [Hightlight]
3 WHERE [Priority]=select Max(Priority)
4 ORDER BY NewID()

OR:

select Top1 *

from [hightlight]

order by Priority,NewId()

Hope my suggestion helps

|||

But Sir,

From your prescribed solution.

Random Rows of Same Priority will select till that priority will expires.

But I want random rows from random priorities.

And records of low priority will select lower then high priority

|||

zeeshanuddinkhan@.hotmail.com:

But Sir,

From your prescribed solution.

Random Rows of Same Priority will select till that priority will expires.

But I want random rows from random priorities.

And records of low priority will select lower then high priority

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID()-- for random pripority8 )9Order byNewID()-- for random rows

Good luck

|||

Can you tell me?

How can this query take care that the high priority will select more time as compare to low priority?

Because after trying your query every priority has equal chance to come.

|||

And by the way.

First try the query on your own end.

Because your mentioned query not fits as I ask.

And Second it has following error.

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

|||

zeeshanuddinkhan@.hotmail.com:

Can you tell me?

How can this query take care that the high priority will select more time as compare to low priority?

Because after trying your query every priority has equal chance to come.

Here it is:

1SELECT TOP 1 * 2FROM Hightlight3WHERE [Priority]in 4 (5select Priority6From Hightlight7Order byNewID(), Priority-- for random pripority8 )9Order byNewID()-- for random rows

Now the chances for high priority will be more for the random rows.

Good luck.

|||

Please check it.

This query has an error.

|||

zeeshanuddinkhan@.hotmail.com:

Please check it.

This query has an error.

Sorry for the small mistake, I forgot to use top.. here is the correction and I am sure it will work with you,

11SELECT TOP 1 *22FROM Hightlight33WHERE [Priority]in44 (55select TOP 1 Priority66From Hightlight77Order byNewID(), Priority-- for random pripority88 )99Order byNewID()-- for random rows10

Good luck.

|||

SELECT *

FROM Highlight

WHERE GetDate() BETWEEN StartDate AND EndDate

ORDER BY rand(cast(cast(newid() as binary(4)) as int))*Priority

That will return all the records that have a startdate before now, and a enddate later than now in a random order weighted by priority. (Priority 1 will be more likely to be near the top than a priority 5, etc). Just add TOP 1 if you only want the first record after you are done verifying that the results are what you want. If you wanted priority 5 to be selected more than priority 1, then add " DESC" to the end of the query.

NewId by itself IS NOT random. It is unique on every call, but it's predictable. You can however, use it as a seed to the rand function, which will make it random (or more random).