Here's one thats had me and a coworker puzzled. Hopefully it's something simple:
TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)
Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message
TABLE B: (List of users)
userid, int
name, varchar(100)
Table B Data:
1, John
2, Mike
I want to extract the most recent message logged per user, i.e.:
name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.
I have been unable to come up with a query that can return just the one value. I've tried variants of:
SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid
including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id ticket_to ticket_from
message
message_id ticket_id message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message FROM tickets INNER JOIN message ON tickets.ticket_id = message.ticket_id GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
I want to select all usernames from tlbUsers which practice a certain sport.I also have a table UsersAndSports:UserID intSportID intI therefore need an inner join statement:select username from(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,ud.username from tblusers usinner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)WHERE us.UserName <>'' )as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes thatI cant make a distinct selection as each RowNum is unique....:SIn case the user would practice 2 sports, the query would return 2 rows...if I place a distinct in front of the username as such:select distinct username fromThe query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..so lets say my startrowindex would be 0 and maximumrow 5if my 1st query (without distinct) was to return:johnjohnjohnjohnmikemikerobmy 2nd query (with distinct) would return:johnmikewhereas I want it to return:johnmikerobWhat can I do?
I have 2 tables. Product, ProductCategory. Product table consists of (productid, productname, producttypeid, createdate, portid) productcategory consists of (productid, categoryid, productcategoryid, categoryid, prodroletypeid, createdate) I want to write sql query to get the unique products. So the count should only give one value/resuls. But when I run the following query, I am getting more than one rows select count(distinct categoryid) as uniquecat from productcategory as pc, product as p where pc.prodroletypeid in ('P', 'F') and pc.createdate <= dateadd(d, 30, getdate()) and p.portid = 100 group by pc.productid
I've got a union query (below)and it returns rows that have duplivate itemno's, descrip's, imsrp3's, and imsrp4's, while the remaining columns are not duplicate for the same row. An Excel report uses this query to populate itself and for a more visually appealing look, I'd like to skip the duplicated columns in the display. I'm not sure how to use the Distinct or Group by in this case, since technically I'm dealing with two separate queries, neither one separately returning any duplicate rows. thanks for any suggestions...
~ select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nowo where nowo.wasrst <='40' union select itemno,descrip,imsrp3,imsrp4,qoh,border,wadcto,wad oco, watrdj,wapddj,wauorg,wauser from nopo where nopo.wasrst <='499'
I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.
Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.
I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.
SELECT * FROM SAMPLE WHERE ret_ref_no in ( SELECT ret_ref_no FROM SAMPLE GROUP BY ret_ref_no HAVING COUNT(*) > 1 )
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index FROM dbo.csr_standards_cmsars ORDER BY ARSFamily
but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups). The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1) Days_in_warehouse 20 30 40
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
I don't know if anyone has encountered this before but here goes:
I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
Hello~, The table has columns like this. ________________________________ time smalldatetime value1 int value2 int ----------------------------------------------------------
for example, .... '2006-11-16 12:00:00',100,200 '2006-11-16 13:00:00',110,210 '2006-11-16 14:00:00',120,220 ....
The record is inserted at every hour.
I want get daily,monthly,yearly average and display the result ordered by time.
I have a stored procedure which selects results based on some date calculations.
In my table I have a status column and two date fields (Approval Date and Signature Date) If the value in the status column says approved I want to select results where approval date - signature date is less than a certain number of days.
If the status is naything other than approved i want to select results where sysdate - signature date is less than the given number of days.
I have the following code which is incomplete. Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there?
Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()
I've managed to get my query to this: Product | Color | Votes ======== ======= ======= Bus Red 10 Bus Blue 5 Train Blue 1Car Red 1 Car Blue 1
I need the most popular color of the product based on the votes for each product, so MAX(Votes) GROUP BY Product solves the Bus and the Train, but I still need a result for the Car? Not really that bothered which Color is picked up, do I need to run another query, because I need another table to join to the results.
Any help would be greatly appreciated. Thanks, Pete.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
I am trying to take the results of a query and re-orient them into separate columns.
select distinct W_SUMMARYDETAILS.FACILITY_ID, W_SUMMARYDETAILS.REPORTING_YEAR, (2011 - 2014, I want these years broken out into columns for each year) W_SUMMARYDETAILS.FACILITY_NAME, W_DEF_SUMMARYDETAILS.REPORTING_PERIOD (2011 - 2013, I want these years broken out into columns for each year) From W_SUMMARYDETAILS full outer join W_DEF_SUMMARYDETAILS on W_SUMMARYDETAILS.FACILITY_ID=W_DEF_SUMMARYDETAILS.FACILITY_ID and W_SUMMARYDETAILS.REPORTING_YEAR=W_DEF_SUMMARYDETAILS.REPORTING_PERIOD
As of now the query puts all the years into a single column -- one for DEF_SUMMARY and another for SUMMARY.
I am looking to create 7 additional columns for all the individual years in the results instead of just two columns.
I have small requirement in my project. I need to display the results of the WHERE clause based on percentage/ranking of exact match.
I mean the result set should be displayed based on percentage match.
For example i have the below table.
create table test ( id int identity(1,1) primary key, ename varchar(10) )
insert into test(ename) select 'REG' insert into test(ename) select 'xyz' insert into test(ename) select 'abc' insert into test(ename) select 'Reg' insert into test(ename) select 'Regsxysn' insert into test(ename) select 'psReg'
I need the output something similar as below
REG Reg Regsxysn psReg
I have tried out with full text indexing but i could'nt get the required output.
What I want to do is run the report which details each enquiry each officer has received but stop running once top 10 officers have the most enquiries are in the results.
The officers are identified using the officer.officer_name field, enquiry.enquiry_number field shows each enquiry record.
i'd like to use ssis on a certain project but am concerned that one of my transformations needs lookup results to be based on actions taken on previous lookups and that the toolkit doesnt really offer something like that.
so, i have a dataflow whose first component extracts certain kinds of data from an xml document.
each row returned by the latter needs a lookup but the results of that lookup may dictate a certain kind of update. The next row's lookup may need to be influenced by the previous row's update.
So I think I have two challenges, 1) combining a lookup and update, 2) making sure the buffer architecture completes one lookup and update before the next lookup begins.
I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated.
My source table has two columns... Policynum and PolicyStartdate and data looks like.. . Policynum PolicyStartdate 123G 01/01/2012 456D 02/16/2012 789A 01/21/2012 163J 05/25/2012
Now my output should return based on 3 parameters..
First two parameters are date range... let say @fromdt and @todt
Third parameter is @policynum
Scenario-1: Enter dates in date range param and leave policynum param blank Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output
Scenario-2: enter policy num in policynum param and don't select any dates Ex: policynum ='456D' It returns 2nd row in the output
Scenario-3: Select dates in date range param and enter policynum in param Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum ='163J'. it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
Can someone please help me interpret this result set below and suggeston way I can speed up my table? What changes should I make?DBCC SHOWCONTIG scanning 'tblListing' table...Table: 'tblListing' (1092914965); index ID: 1, database ID: 13TABLE level scan performed.- Pages Scanned................................: 97044- Extents Scanned..............................: 12177- Extent Switches..............................: 13452- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 90.17% [12131:13453]- Logical Scan Fragmentation ..................: 0.86%- Extent Scan Fragmentation ...................: 2.68%- Avg. Bytes Free per Page.....................: 1415.8- Avg. Page Density (full).....................: 82.51%DBCC execution completed. If DBCC printed error messages, contact yoursystem administrator.Thank you.
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below, 2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1'; @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0 DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY, schoolID int NOT NULL, UNIQUE(schoolID,lockID) )
[Code] ....
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?