none
SQL Server SKIP statement with VB6

    Question

  • Hi All,

    I am trying to perform a complex query against a SQL Database in VB6 using ADO.

    Basically this is what I am  looking for;

    Select all records from the table, ordered by ImageDateTime DESC and grouped by key1, then I want to skip the first xx records of each group.

    Trying to figure out this syntax is making me OLD :)

    Any help would be appreciated.

     

    Thursday, August 26, 2010 5:58 PM

Answers

  • If you need to keep top 10 records per key add PARTITION BY Key in the ROW_NUMBER() function, e.g.

    row_number() over (partition by Key order by Date Desc)

    ------------------

    Making the code universal for different back-ends is not an easy task. You may try switching to LINQ or Entity Framework, but I'm not sure which backends these technologies support. Alternative solution will be to create different query for the specific Back-end (e.g. move the logic into the Data Access class). The SP is a good solution for SQL Server. I know that in MySQL you need to use LIMIT keyword. In Access I'm not sure - but I know someone very proficient in Access, so I may ask her.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by GaryBouchard Friday, August 27, 2010 4:11 PM
    Friday, August 27, 2010 3:39 PM
    Moderator

All replies

  • Give a look at the analytic functions in books online -- especially RANK() and ROW_NUMBER().  You can use one of these to generate sequence numbers that you can use.  Then it is a matter of selecting records that properly target sequence numbers.
    Thursday, August 26, 2010 6:01 PM
    Moderator
  • Thank you Kent. I do have a SeqNo column in the table already... is that what you mean?
    Thursday, August 26, 2010 6:31 PM
  • if u wants to skip first 10 records for each group, then u can write like

    ;with cte as
    (
    select *,row_number() over (partition by key1 order by ImageDateTime desc) as rn from TableName
    )
    select * from cte where rn > 10

    Thursday, August 26, 2010 6:36 PM
  • Thank for the input ramireddy.

    Thinking about this a bit more, I wonder if I am approaching this the right way.

    What I am trying to do is provide a purge/archiving function in my program. I have a table of images and want to provide a way to archive older images that may be attached to a record key. For example a record (key=12345) might have 5 images associated with the specific key and I want to keep the ## most recent images, archiving the rest of them.

    Under normal circumstances I would just query the record by key and do this, however the process involved is creating a list of records to be archived/purged. I then go through each record in the resulting recordset to ensure that the image file (path & name contained in the record) physically exists, then add it (the record) to the Xceed Backup files list component, which handles the rest.

    I might be just convoluting an obvious solution :)

     

    Thursday, August 26, 2010 7:04 PM
  • what is the front-end language u r using? .net?? anyway the approach will be

    1. Get all the FilesNames of those images in that directory into a string array and combine them using "," with Join() method. ( in other words, get comma seperated list of those file Names) and pass that list of names to DB.

    2. Use some split() function in sql and get all the images except the specified no of images which are there in the list of files. query will be like 

    ;with cte as
    (
    select *,row_number() over (partition by key1 order by ImageDateTime desc) as rn from TableName T
    inner join fn_split(@FileNames,',') t1 on T.FilePath = t1.FilePath
    )
    select * from cte where rn > 10

    3. Now it returns all those files u can archive. Now get these records to front-end and archive all these files. as soon as you archiving, u can update the status back to DB. Or you put all these names in a stringBuider or something and update all at once. Maintain the transaction related things also...

     

     

    otherwise u can do it in reverse also........ first get all file namess in front-end.. check the existing files and archive after the specified count and re-update those records status..

     

    Thursday, August 26, 2010 7:28 PM
  • ramireddy,

    As I mentioned in the original post I am using VB6. The approach you have is interesting and I'll give it some thought. The method right now does a query for records between two dates, which was easy, but now I want to add the ability to leave X number of most recent images and archive the rest which may or may not be in conjunction with the dates.

    With the original method, when the recordset was returned I just went through the recordset and retrieved the information I needed so that is not the issue... I just need to figure out the syntax to return a recordset that I can go through. Its the query that I need to figure out, so that the query only returns the qualifying records.

    When I try to run various SQL statements in the SQL editor of SQL2005 it does not like the 'SKIP n' (n=# of records) for some reason... it does not say why. That would serve my purposes I believe but I cant figure out how to use it :)

    I dont know if this would help but here my code;

    Public Function BuildArchiveList(sErrorMsg As String) As Boolean

    '--------------------------------------------------------------------------------
    ' Project    :   cpcAdmin
    ' Procedure  : BuildArchiveList
    ' Description: This function goes through the database to locate records that match the date range specified on the form and builds a backup list _
      which contains records that fall within the DateTime range, and determines if the Filename actually exists before adding the Filename to the FilesSelection1 Control.
    ' Created by : gary
    ' Machine     : DEVMACH
    ' Date-Time  : 02/17/2005-09:53:45
    '
    ' Parameters : sErrormsg (String$) Text returned from the function on error.
    '--------------------------------------------------------------------------------
    Dim rsADO As New ADODB.Recordset
    Dim sSqlString As String
    Dim sFullFilePath As String
    Dim sStartDate As String
    Dim sEndDate As String

    On Error GoTo BuildArchiveList_ErrHandler
    IdentifyModule clsName, "BuildArchiveList"
    sErrorMsg = vbNullString
    If dtpStartingDate > Date Then
        sErrorMsg = "The Starting date cannot be greater than today's date."
        BuildArchiveList = False
        Exit Function
    End If
    If dtpEndingDate > Date Then
        sErrorMsg = "The Ending date cannot be greater than today's date."
        BuildArchiveList = False
        Exit Function
    End If
    '8/26/2010 GLB- If the start and end dates are the same, we just return all records
    If dtpEndingDate = dtpStartingDate Then
        sSqlString = "Select * from Images " 'where ImageDateTime= " & dtpStartingDate
        TraceLog traceALL, indentONE, "From Date is equal to Ending Date. Selecting all records."
    Else
        ' calls mySQLDate function from module MiscFunctions which converts the date value to something that SQL can use for comparison.
        sSqlString = "Select * from Images where (ImageDateTime >= " & mySQLDate(dtpStartingDate) & ") AND (ImageDateTime <= " & mySQLDate( _
                dtpEndingDate) & ")"
    End If
    If CInt(txtNumberToArchive) > 0 Then
        sSqlString = sSqlString & " Order By ImageDateTime Desc SKIP " & CInt(txtNumberToArchive)
    End If
    Set rsADO = ReturnRecordSet(sSqlString)
    TraceLog traceDEVL, indentONE, "SqlString= " & sSqlString
    TraceLog traceDEVL, indentONE, "Returned Row Count: " & rsADO.RecordCount
    If rsADO.RecordCount > 0 Then
        With rsADO
            lblArchivestatus(8).Caption = "Database Record Count: " & .RecordCount
            .MoveFirst
            Do While Not .EOF
                sFullFilePath = vbNullString
                sFullFilePath = !ImagePath & !ImageId & ".cpc"
                If FileExists(sFullFilePath) Then
                    sFullFilePath = !ImagePath & !ImageId & ".cpc" & vbCrLf
                    FileSelection1.AddFilesToProcess sFullFilePath, False
                    sFullFilePath = !ImagePath & !ImageId & ".sml" & vbCrLf
                    FileSelection1.AddFilesToProcess sFullFilePath, False
                    sFullFilePath = !ImagePath & !ImageId & ".thb" & vbCrLf
                    FileSelection1.AddFilesToProcess sFullFilePath, False
                    sFullFilePath = !ImagePath & !ImageId & ".tif" & vbCrLf
                    FileSelection1.AddFilesToProcess sFullFilePath, False
                    sFullFilePath = !ImagePath & !ImageId & ".bmp" & vbCrLf
                    FileSelection1.AddFilesToProcess sFullFilePath, False
                Else
                    TraceLog traceALL, indentONE, sFullFilePath & " was not found on disk"
                End If
                .MoveNext
            Loop
        End With
        BuildArchiveList = True
        sErrorMsg = vbNullString
    Else
        ' No Qualifying records
        sErrorMsg = "No Qualifying Records to Archive"
        BuildArchiveList = False
    End If
    rsADO.Close
    Set rsADO = Nothing
    Exit Function
    BuildArchiveList_ErrHandler:
        AppError.RaiseError "frmBackup", "BuildArchiveList", err.Number, err.Description
        sErrorMsg = err.Description
        Set rsADO = Nothing
    End Function

    Thursday, August 26, 2010 8:12 PM
  • Are you using SQL Server or MySQL? Can you simply create a stored procedure to pass StartDate, EndDate, NumberOfRecordsToKeep?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, August 26, 2010 8:44 PM
    Moderator
  • Are you using SQL Server or MySQL? Can you simply create a stored procedure to pass StartDate, EndDate, NumberOfRecordsToKeep?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Actually Naomi, I can and do use Access, MSSQL (>=2000)  and MySQL 4... the backend database at this point is connected to via an ODBC DSN connection.

    Even if I do use a stored procedure, which I probably wont need, I still have to figure out the syntax to get the resultset I need :) I am using ADO to do the database work.

    Thursday, August 26, 2010 8:52 PM
  • > When I try to run various SQL statements in the SQL editor of SQL2005 it does not like the 'SKIP n' (n=# of records) for some reason... it does not say why. That would serve my purposes I believe but I cant figure out how to use it :)

    Any particular reason SQL Server should like it? I'm afraid that SQL Server is just like any other software. It understand the syntax it understands, and if you try something else, it has no single clue what you are talking about. SKIP may be a keyword in conjunction with BACKUP/RESTORE, but there is certainly no such operator in SELECT statements.

    The conditions with row_number posted by several should do the trick for you.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Thursday, August 26, 2010 10:09 PM
  • The reason I suggested to create a stored procedure is that your code will become much neater and you also will not do string concatenation:

     

    create procedure GetRecordsToArchive
    
    (@StartDate datetime,
    
    @EndDate datetime,
    
    @NumberOfRecordsToKeep int = 0)
    
    as
    
    begin
    
    with cte as (Select *, row_number() over
    
    (order by ImageDateTime DESC) as Row
    
    from Images
    
    where ImageDateTime >= @StartDate AND
    
    ImageDateTime <=@EndDate)
    
    select * from cte where Row > @NumberOfRecordsToKeep -- get older records
    
    end
    
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, August 26, 2010 10:29 PM
    Moderator
  • > When I try to run various SQL statements in the SQL editor of SQL2005 it does not like the 'SKIP n' (n=# of records) for some reason... it does not say why. That would serve my purposes I believe but I cant figure out how to use it :)

    Any particular reason SQL Server should like it? I'm afraid that SQL Server is just like any other software. It understand the syntax it understands, and if you try something else, it has no single clue what you are talking about. SKIP may be a keyword in conjunction with BACKUP/RESTORE, but there is certainly no such operator in SELECT statements.

    The conditions with row_number posted by several should do the trick for you.

    Erland,

    Perhaps I am not reading this correctly. If I search SQL2005 Books Online and go to this page, there is a SKIP;

    http://msdn.microsoft.com/en-us/library/bb738680.aspx

    Perhaps this could be reveresed by using the TOP keyword?
     

    Thursday, August 26, 2010 10:37 PM
  • You're reading documentation for Entity Framework, not for the SQL Server. 

    I've tried 

    SELECT p.ListPrice FROM AdventureWorks.Production.Product 

        AS p order by p.ListPrice --SKIP 5

    SELECT VALUE p FROM AdventureWorksEntities.Products 
      AS p order by p.ListPrice SKIP(@price)

    And it gave me an error if I uncomment skip 5. This is not T-SQL syntax.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, August 26, 2010 10:56 PM
    Moderator
  • Perhaps I am not reading this correctly. If I search SQL2005 Books Online and go to this page, there is a SKIP;

    http://msdn.microsoft.com/en-us/library/bb738680.aspx

    Perhaps this could be reveresed by using the TOP keyword?

     
    As Naom said, that's the Entity Framework, which has it's on flavour of SQL.

    But it makes me worried, if you said that you say you search SQL Server 2005 Books Online, and you ended up in Entity SQL. Is there any chance that you can give the exact steps for how you searched? If Microsoft has botched search that's really bad.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Friday, August 27, 2010 7:33 AM
  •  
    As Naom said, that's the Entity Framework, which has it's on flavour of SQL.

    But it makes me worried, if you said that you say you search SQL Server 2005 Books Online, and you ended up in Entity SQL. Is there any chance that you can give the exact steps for how you searched? If Microsoft has botched search that's really bad.

    Erland,

    Yes, I openned SQL2005 Express on my desktop, selected HELP/Index and when the screen came up I entered SKIP in the search box and that is the page it gave me.

    Friday, August 27, 2010 2:49 PM
  • Make sure to use SQL Server instead of All Technologies. I actually don't use Help in SSMS, I prefer to google my question - just add T-SQL at the end - in 99% of cases the first link that comes is BOL.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 2:58 PM
    Moderator
  • The reason I suggested to create a stored procedure is that your code will become much neater and you also will not do string concatenation:

    create procedure GetRecordsToArchive
    (@StartDate datetime,
    @EndDate datetime,
    @NumberOfRecordsToKeep int = 0)
    as
    begin
    with cte as (Select *, row_number() over
    (order by ImageDateTime DESC) as Row
    from Images
    where ImageDateTime >= @StartDate AND
    ImageDateTime <=@EndDate)
    select * from cte where Row > @NumberOfRecordsToKeep -- get older records
    end
     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Naomi,

    Thank you so much for your input, I really appreciate it. I totally understood what you meant by the stored procedure and I am not adverse to doing that... my dilemma was not being able to figure out the correct syntax to even get a resultset.

    I tried out your SP this morning. Unfortunately, the result is not what I am looking for, but definately on the right track. It appears the SP you created just returns all images that fall between the dates, then skips the top X records from the entire query. I need to go a step further than that by only keeping the top x records from each key1 and archiving the rest. You obviously know what you are doing here, which puts me at a severe disadvantage :)

    I will keep digging. Using a SP sounds like a good idea, however the backend database can be either MS-Access, MySQL4, or MSSQL, so whatever solution I find needs to be able to work with all three databases, which is why I was looking for a method I could use in VB6.

    Again, thank you for your input.

    Friday, August 27, 2010 3:28 PM
  • If you need to keep top 10 records per key add PARTITION BY Key in the ROW_NUMBER() function, e.g.

    row_number() over (partition by Key order by Date Desc)

    ------------------

    Making the code universal for different back-ends is not an easy task. You may try switching to LINQ or Entity Framework, but I'm not sure which backends these technologies support. Alternative solution will be to create different query for the specific Back-end (e.g. move the logic into the Data Access class). The SP is a good solution for SQL Server. I know that in MySQL you need to use LIMIT keyword. In Access I'm not sure - but I know someone very proficient in Access, so I may ask her.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by GaryBouchard Friday, August 27, 2010 4:11 PM
    Friday, August 27, 2010 3:39 PM
    Moderator
  • If you need to keep top 10 records per key add PARTITION BY Key in the ROW_NUMBER() function, e.g.

    row_number() over (partition by Key order by Date Desc)

    ------------------

    Making the code universal for different back-ends is not an easy task. You may try switching to LINQ or Entity Framework, but I'm not sure which backends these technologies support. Alternative solution will be to create different query for the specific Back-end (e.g. move the logic into the Data Access class). The SP is a good solution for SQL Server. I know that in MySQL you need to use LIMIT keyword. In Access I'm not sure - but I know someone very proficient in Access, so I may ask her.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    Naomi,

    That last tidbit did the trick...now the fun part figuring out how to make this work across the different backends!

    Thank you very much for your time everyone!

    Friday, August 27, 2010 4:12 PM
  • Here is the query that will most likely work in Access and in T-SQL:

     

    select * from myTable T 
    where ID not in 
    (select top 10 ID from myTable X 
    where X.Key = T.Key order by Date DESC)
    
    It may work the same in MySQL too.

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Friday, August 27, 2010 4:15 PM
    Moderator
  • > Yes, I openned SQL2005 Express on my desktop, selected HELP/Index and when the screen came up I entered SKIP in the search box and that is the page it gave me.

    Was that the entry box for the Index, or did you use the Search tab?

    Personally, I have set Books Online to only use local content, and I have also set some filters to only show certain technologies. (Although this filtering works better in Books Online for SQL 2008.)

    In SQL 2005 you also have to be careful so that you don't end up on pages for Compact Edition, which can be big source for confusion.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Friday, August 27, 2010 9:51 PM
  • If you need to keep top 10 records per key add PARTITION BY Key in the ROW_NUMBER() function, e.g.

    row_number() over (partition by Key order by Date Desc)

    Naomi,

    Again, thank you for the information. I have a question that perhaps you can answer... I have been googling and looking at documentation, and by virtue of my not finding the needed information I am assuming that I cannot do this, but is it possible to "Partition By" 2 different fields?

    I have a key field that is currently being partitioned but would like to add another partition by column in the same statement... (.g. Partition by KEY1, ImageType) so I can reset the row number not only by the KEY1 column but also ImageType.

    Judging from the description of the function, it breaks the row numbers again when it encounters a new value, unless I am reading this wrong...

    USE

     

    [cpcimages]

    GO

    /****** Object: StoredProcedure [dbo].[GetRecordsToArchive] Script Date: 09/14/2010 13:26:34 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    ALTER

     

    procedure [dbo].[GetRecordsToArchive]

    (

     

    @StartDate datetime,

    @EndDate

    datetime,

    @NumberOfRecordsToKeep

    int = 0)

    as

    begin

    with

     

    cte as (Select *, row_number() over (partition by Key1, ImageType order by ImageDateTime Desc) as Row

    from

     

    Images

    where

     

    ImageDateTime >= @StartDate AND

    ImageDateTime

    <=@EndDate)

    select

     

    * from cte where Row > @NumberOfRecordsToKeep -- get older records

    end

     

     

    Tuesday, September 14, 2010 5:49 PM
  • Yes, you're reading the documentation correctly. When you partition by 2 fields, the Row starts from 1 when the new value for either of the field begins.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, September 14, 2010 6:36 PM
    Moderator
  • Yes, you're reading the documentation correctly. When you partition by 2 fields, the Row starts from 1 when the new value for either of the field begins.

    Naomi, Thank you... does that mean the way I have it above is the correct syntax? I cannot find any examples of using more than one field :)

     

    Wednesday, September 15, 2010 7:01 PM
  • Yes, your syntax was correct. 

    select *, row_number() over (partition by Field1, Field2, .., FieldN order by FieldM, FieldM1, FieldM2) as Row from myTable


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 15, 2010 7:05 PM
    Moderator