Top 1 distinct or something like that
-
Hi, Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid like Typid details 1 details1 2 details2 .... 40 details40 Does that make sense?
-
Hi, Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid like Typid details 1 details1 2 details2 .... 40 details40 Does that make sense?
Use distinct
Select distinct typeid,details from auditlog order by typeid
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
Use distinct
Select distinct typeid,details from auditlog order by typeid
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
Thanks for the reply. I don't think I made it clear in my original post so apologies, but the details column could be anything (it's actually an XML fragment) so distinct will bring back all rows anyway or at least more than 1 row per eventid. I think I'm after a sort of "top 1 distinct" combination.
-
Thanks for the reply. I don't think I made it clear in my original post so apologies, but the details column could be anything (it's actually an XML fragment) so distinct will bring back all rows anyway or at least more than 1 row per eventid. I think I'm after a sort of "top 1 distinct" combination.
So, can you post data structure in your table and result which you want to get from that data?
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.cacttus.com
-
Hi, Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid like Typid details 1 details1 2 details2 .... 40 details40 Does that make sense?
What I'd like to do is get the top 1 details for each typeid How do you know that a particular detail is 'top' among its peer details? Does picking a particular item matter at all? If it does not matter which row you pick as your 'top', try this:
select typeid, min(details) from auditlog group by typeid
it will decide that a detail is 'top' if its description comes first alphabetically. If this is not what you need, you would have to provide another column to determine which detail among many possibilities for the same
typeidis to be selected. -
Hi, Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid like Typid details 1 details1 2 details2 .... 40 details40 Does that make sense?
Without sample data its hard to give you a best solution, give this a try
select distinct a.typeid, (select top 1 details from auditlog where typeid = a.typeid) [details]
from auditlogLobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
What I'd like to do is get the top 1 details for each typeid How do you know that a particular detail is 'top' among its peer details? Does picking a particular item matter at all? If it does not matter which row you pick as your 'top', try this:
select typeid, min(details) from auditlog group by typeid
it will decide that a detail is 'top' if its description comes first alphabetically. If this is not what you need, you would have to provide another column to determine which detail among many possibilities for the same
typeidis to be selected.I agree with the above post. You need a column like, timestamp, along with your other columns to group the TypeIds. This way you could do something like max(timestamp) and get the most recent logged event. Good luck.
-
What I'd like to do is get the top 1 details for each typeid How do you know that a particular detail is 'top' among its peer details? Does picking a particular item matter at all? If it does not matter which row you pick as your 'top', try this:
select typeid, min(details) from auditlog group by typeid
it will decide that a detail is 'top' if its description comes first alphabetically. If this is not what you need, you would have to provide another column to determine which detail among many possibilities for the same
typeidis to be selected.This did it thanks. apologies for the vagueness, well I knew what I meant. To hopefully clear up any questions - the table can have many thousands of rows each one holds the details of application events used for auditing purposes. There are about 40 event types that are logged (eventid) and the details column holds the data associated with a particular event e.g
this one describes a simple page view. Other columns are things like hold details of user ids, timestamps etc but in this instance were not required I jut needed one example of each event type to test a reporting application. Reading that I could have been clearer in my original post. Thanks again everyone.