Limit the rows returned in sql query RSS

• • Reply
  

  davidsa

  Member

  210 Points

  126 Posts

  Limit the rows returned in sql query

  Dec 30, 2012 12:32 AM|LINK

  How do you limit the number of rows returned a sql query say to 5 in SQL CE?

  Here's my query so far:

  var query = "SELECT * FROM Events WHERE StartDate >= GetDate()";

  Also, in my query it returns events in the future but not for today. What am I missing?
  
  

• • Reply
  

  Anil Srivast...

  Member

  442 Points

  292 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 02:06 AM|LINK

  var query = "SELECT top 5 FROM Events WHERE StartDate >= GetDate()";

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 02:27 AM|LINK

  davidsa

  Also, in my query it returns events in the future but not for today. What am I missing?

  You are not taking the time component of GetDate() into account.

• • Reply
  

  davidsa

  Member

  210 Points

  126 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 02:40 AM|LINK

  I tried top 5 before posting but that only returns the first 5 rows in the table. It doesn't check the WHERE condition.

• • Reply
  

  davidsa

  Member

  210 Points

  126 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 02:56 AM|LINK

  Dan Bracuk

  You are not taking the time component of GetDate() into account.

  I see what you are saying but not sure how to fix it. Suggestions?

• • Reply
  

  Dan Bracuk

  Contributor

  3970 Points

  1096 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 03:03 AM|LINK

  davidsa

  I see what you are saying but not sure how to fix it. Suggestions?

  You could cast getDate() as a date to get rid of the time component.  There are some answers in this thread about selecting the top 5.  They are missing an order by clause.  I'll leave you to figure that out.

• • Reply
  

  davidsa

  Member

  210 Points

  126 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 04:12 AM|LINK

  Dan Bracuk

  You could cast getDate() as a date to get rid of the time component.  There are some answers in this thread about selecting the top 5.  They are missing an order by clause.  I'll leave you to figure that out.

  I think I've got the top figured out but cannot cast to date in SQL CE - datetime works but that defeats the purpose. What is the SQL CE method for casting to date?

  SELECT TOP 10 * FROM Events WHERE CAST(StartDate AS Datetime) >= CAST(GETDATE() AS DateTime) ORDER BY StartDate

  
  
  

  • Edited by davidsa on Dec 30, 2012 04:13 AM
• • Reply
  

  Afzaal.Ahmad...

  Contributor

  2661 Points

  1040 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 08:02 AM|LINK

  The SELECT query will have a TOP 5 statement. Or else you can use max(Time) as Time in your query. But max(Time) will give you only one result.

  So using top 5 willl be good.

  var query = "SELECT top 5 FROM Events WHERE StartDate >=@0";

  var result = Database.Open("Your_Database").Query(query, DateTime.Now);

  Please "Marks As Answer" if any answer helped you out!
  ~~! FIREWALL !~~
• • Reply
  

  GmGregori

  Contributor

  5470 Points

  737 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 09:34 AM|LINK

  davidsa

  What is the SQL CE method for casting to date?

  Cast to integer

  SELECT TOP 10 * FROM Events WHERE StartDate >= CAST(GETDATE() AS INTEGER) ORDER BY StartDate

  • Marked as answer by davidsa on Dec 31, 2012 07:51 PM
• • Reply
  

  davidsa

  Member

  210 Points

  126 Posts

  Re: Limit the rows returned in sql query

  Dec 30, 2012 10:50 PM|LINK

  GmGregori

  Cast to integer

  How would date be seperated by casting to an integer?