Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tictoc
Starting Member
7 Posts |
 Posted - 2011-03-06 : 18:56:58
|
| Trying to find a better way than creating seperate selects for each field. Here is the basic info.The query of selecting top 1 is to ensure that you are only selecting the top latest record for a specific request which let get one to one record of each application request. As you know, a single request can have multiple sKeys, and each skey may have one or multiple fields populated for a specific application request...For example skey 1, might have manufacturer, MSI ID populated but not the display name for request # xyz And skey 2 have null for manufacturer, null for MSI ID and value for display nameThe query ensure you are selecting the most updated value for each field you select.... [Manufacturer]=(select top 1 [20Manufacturer] from dbo.RPT_CMP_User_RAW where [0skey] = wf.[0skey] Order by RowDateEntered Desc )It selects the most updated value for manufacturer for each request...So I have to doMarketplace Order ID]= ( select top 1 [3Marketplace Order ID] from dbo.RPT_CMP_WF_RAW where [0skey] = wf.[0skey] Order by RowDateEntered Desc),[RequestID]= ( select top 1 [4RequestID] from dbo.RPT_CMP_WF_RAW where [0skey] = wf.[0skey] Order by RowDateEntered Desc),[Application Display Name] = (select top 1 [101Request Name] FROM dbo.RPT_CMP_USER_Raw where [0skey] = wf.[0skey] and [101Request Name] is not null order by RowDateEntered desc)For each and every field I need. There are about 150 of them AND 1.6 Million records.Is there a better approach to this? Some examples would be greatly appreciated. |
|
|
chris_n_osborne
Starting Member
34 Posts |
Posted - 2011-03-06 : 19:47:20
|
| The requirements of "top" and "latest" are different.TOP 1 implies you are picking the first row in any set that the database has provided to you.Latest implies you are picking the maximum value, i.e. MAX().Since your subqueries are using the DESC keyword on ORDER BY, it looks like you are trying for the MAX() value in each column, in a roundabout way.The following is an untested guess on my part:,MAX([3Marketplace Order ID]) AS [Marketplace Order ID],MAX([4RequestID]) AS [RequestID],(select MAX([101Request Name]) FROM dbo.RPT_CMP_USER_Raw where [0skey] = wf.[0skey] and [101Request Name] is not null) AS [Application Display Name]The numerically titled columns indicate the presence of repeating columns. If so and if you are in control of the database, you might want to consider normalizing the table structure. |
 |
|
|
tictoc
Starting Member
7 Posts |
Posted - 2011-03-06 : 20:26:55
|
| Thank you for the reply and the examples. Much appreciated.yes it's a Crazy database and I am working with the team to normalize the data. It's imported data from another system.The system is a Workflow system and has a Customer and a Workflow side to it but you need both side to find out everything about a products status and each product has 14 steps plus each step is a seperate entry. Hence the need to use the Top 1 (this was implemented by the database developer) in order to see all of the fileds populated with data rather than NULL's. The fields numbers are a reference for the import scripot he has running.The RowDateEntered is the latest update date and time and the 0skey is the primary key in all tables. Sorry that is VERY compressed and you are very right on the normalize data part and I am working on it. The first part though for reporting, which is what this is used for, is removing un-needed data from the imports then I was going after the rest.I'll work on getting a better view and post it up later. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-03-06 : 21:03:11
|
Chris - TOP 1 can imply latest when you order the results descending instead of ascending.Lookup CROSS APPLY in Books Online, which is probably the method you are looking for. A simple example would be:SELECT ... ,wf.MarketPlaceOrderID ,wf.RequestID ,... FROM dbo.RPT_CMP_USER_Raw ur CROSS APPLY (SELECT TOP 1 [3Marketplace Order ID] As MarketPlaceOrderID ,[4RequestID] As RequestID FROM dbo.RPT_CMP_WF_RAW wf WHERE wf.[0skey] = ur.[0skey] ORDER BY wf.RowDateEntered desc) AS t1 WHERE ... Jeff |
|
|
|
tictoc
Starting Member
7 Posts |
|
|
|
|
|
|
|
Select Top 1