Hi everyone! More questions of course!!

Lets say i have a populated datatable with 100 records.

Lets say one of the columns is "Date",

I know i can do the following:

Dim dv as dataView(_dt)
dv.Sort = "Date DESC"

which will sort the records by Date Desc, which is good, now i want to simply grab the top 5 records and return them such that those top 5 can be binded to a datalist! Do i put the top 5 in a new datatable? a dataview? what?

It seems so simple, yet i'm having problems with it!

why don't you just simply use the sql script to get the top 5 record?

example:
SELECT TOP 5 date from tbl_date ORDER BY date DESC

well, yes, i know that seems like the obvious, but here is a simple scenario where i might want the top 5. Lets say i have a complex query that returns me 50 records of relevant info, and the information can be sorted 5 different ways, i simply just want to get the top 5 without re-inventing the complex query. I would rather take the query and just retreive a view or snapshot of the top 5 according to some criteria.

Can you define the requirement more explicitly? Are you saying that you want to sort the "top 5" results in 5 different ways? Isn't the "top 5" dependant on how you are sorting? Or is the criteria for determining the "top 5" based on some explicit single sort, and then you want to sort those 5 results?

Ok, lets say i have some complex algorithm populate a dataset that contains 1 table "TopCompanies" and it has 100 records of the top 100 companies in a given state. Now, Instead of re-inventing the wheel to calculate only the top 5 companies, i simply want to read in this xml file using the dataset.ReadXML(Path), and give it some sort criteria and select only the top 5. So for instance, lets say i wanted the top 5 money making companies, I would simply sort by Revenue DESC, then take only the top 5 from the list.

I know that if i do a dataview of a dataset, i can give it some sorting criteria, now i just need to select the top 5 from the list, not all 100. Is that more clear? thanks

Flyin

Just curious, is 'TOP' an ANSI standard?

No, TOP is a MS-SQL feature. Some other DB system have a similar feature, in MySQL I think it's called "limit", but there is not SQL Language standard.

Flyin-
I'm sorry I can't be of more help. I haven't worked with the more involved bits of ADO.net yet. I usually let the database handle my sorting and limiting. But I see what you are driving at.

This is pretty klugey, but couldn't you perform the select on the datatable, then delete all the rows past the 5th before you bind the result? Messy, but it might get the job done and still be faster than re-querying.

thanks for the help peter, and everyone else, yes this is a quest i will have to find the answer for. If and when i do find it i'll post it back here for all to see just incase anyone runs into the problem! thanks

Flyin

Hi flyin,

If I were you, I'd create a method that accepts a DataSet and a Sort String and returns a DataTable (or DataSet or view or whatever seems to make the most sense).
This method sets the Sort expression, extracts the first 5 rows by looping through its View (using DataRowView items for example) and then adds those rows to the return table / view whatever.

This way, you can bind your TOP 5 items like this:

MyDataGrid.DataSource = GetTop5(MyCompleteDataSet, "Date DESC")


HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

hey everyone, just incase you were wondering i found a temp solution that works.

It involves two features of the datatable:
1. Clone() which basically copies the schema of the table from the old table into the new.
2. ImportRow() This will copy one row from a table into a new one. So simple example of this in action.
Lets take imar's function name

Function GetTop5(dt, RowCount, _Start)
    Dim _table as DataTable
    _table = dt.Clone()
    For i = _Start To _Start + RowCount
        If i >= dt.Rows.Count Then
            Exit For
        Else
            _table.ImportRow(dt.Rows(i))
        End If
    Next

    Return _table
end function

there it is. If anyone has any suggestions for updates to this they are very welcomed!