I am trying to create a view that shows the first occurance of a particular value in a column.
For example, I have a table that contains multiple part numbers that can be associated with mulitple customers. The first occurance of the part number in the table is associated with the main customer for that part number. I want this view to only show the main customer for each part number.
I am able to do this in Access using the following SQL statement:
SELECT dbo_CustPattMast.PATTERN_NUM, First (dbo_CustPattMast.CUST_NUM)
FROM dbo_CustPattMast
GROUP BY dbo_CustPattMast.PATTERN_NUM
When trying to create the view in the SQL Manager, it will not allow me to use the First statement.
I have a table that contains the following records: ptrecidpaidbyamtchequenoname 4791A X A1200097760LOWE, Bernard 4791A X A4380097760LOWE, Bernard 4791A X A620106406LOWE, Bernard
I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.
The desired result from this query would return: 4791A X A1200097760LOWE, Bernard
I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?
select top 1 MWh from ( select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID ) as [Elleverantor 2 MWh]
But when I put in my long select it doesnt work. Also the long script works fine without these rows inserted. I hope someone can tell me why. //Tommy
I post the entire script and the error message can be found below.
Msg 156, Level 15, State 1, Line 59 Incorrect syntax near the keyword 'select'. Msg 170, Level 15, State 1, Line 114 Line 114: Incorrect syntax near '-'.
SELECT tDeklaration.Diarienummer, IO.OrgNr, IO.OrgNamn, tDeklaration.KontaktpersonNamn, tDeklaration.KontaktpersonEpost, tDeklaration.KontaktpersonTele, (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) as [Egen producerad], -- Hämta ut Orgnamnet för en Elleverantör. Finns det inte i Tabellen ta intressentID:t och hoppa iväg till IREG. (select Case When (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL then (Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNamn from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID = (SELECT top 1 IntressentID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) ELse (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 Namn], (select Case When (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL then (Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNr from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID = (SELECT top 1 IntressentID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) ELse (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 OrgNr], (Select MWh from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) as [Elleverantor 1 MWh],
-- Here is the problem....... select top 1 MWh from ( select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID ) as [Elleverantor 2 MWh],
(select (select sum(tDeklarationElleverantor.MWh) from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID) + (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) ) as [Summa använd och vidare fakturerad], ElTillverkning AS Tillverkningsprocess, ElDel AS [Tillverkningsprocess del av år], ForsaljningsVarde AS Försäljningsvärde, (select Case When ElTillverkning > 0 or Forsaljningsvarde > 0 Then ElTillverkning/Forsaljningsvarde ELse 0 End) As Kvot, (select Case When ElTillverkning <= 0 or Forsaljningsvarde <= 0 Then 0 When ElTillverkning/Forsaljningsvarde < 40 then 0 When ElTillverkning/Forsaljningsvarde >= 40 and ElTillverkning/Forsaljningsvarde < 50 then ElTillverkning/Forsaljningsvarde * 0.5 When ElTillverkning/Forsaljningsvarde >= 50 and ElTillverkning/Forsaljningsvarde < 60 then ElTillverkning/Forsaljningsvarde * 0.75 ELse ElTillverkning/Forsaljningsvarde End) as [Undantagen elmängd],
(select (select SUM(tHjalpkraft.Bruttoproduktion * tSchablonAr.Schablon)/100 from tHjalpkraft INNER JOIN tSchablonAr ON tHjalpkraft.KraftslagID = tSchablonAr.KraftslagID where tHjalpkraft.DeklarationID = tDeklaration.DeklarationID and tSchablonAr.Ar=2007) + (select SUM( tFaktiskHjalpkraft.NettoProduktion) from tFaktiskHjalpkraft where tDeklaration.DeklarationID = tFaktiskHjalpkraft.DeklarationID)) AS [Hjälpkraft],
--Summa avdrag för vidare fakturerad el (ny kolumn, summan under rubrik 5)
(select SUM(tFornybarFrom07.Fornybar) from tFornybarFrom07 where tFornybarFrom07.DeklarationID = tDeklaration.DeklarationID) AS [Förnybar], tDeklaration.Kvotplikt, tDeklaration.BegartAnnullerat as Annullering, tDeklaration.Ovrigt as [Övriga Upplysningar], tDeklaration.Kvotplikt - tDeklaration.Annullerat AS Saknas, tDeklaration.DeklarationStatusID, tDeklarationStatus.StatusIntern FROM tDeklaration INNER JOIN tDeklarationStatus on tDeklaration.DeklarationStatusID = tDeklarationStatus.DeklarationStatusID INNER JOIN tRegistreradKvotpliktIntressent ON tDeklaration.RegistreradKvotpliktID = tRegistreradKvotpliktIntressent.RegistreradKvotpliktID INNER JOIN tIntressentforadOrg ON tIntressentforadOrg.IntressentforadOrgID = tRegistreradKvotpliktIntressent.IntressentForadOrgID LEFT OUTER JOIN intressentregister_skarp.dbo.tIntressentOrganisation IO ON IO.IntressentID = tIntressentforadOrg.ForadAgarID --left outer join tDeklarationElleverantor on tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID WHERE (tDeklaration.AktuellIntern = 1) AND (tDeklaration.KategoriID = 3) AND (tDeklaration.DeklarationStatusID <> 1) AND (tDeklaration.DeklarationStatusID <> 3)and tdeklaration.kvotpliktar=2007 order by IO.OrgNamn
Declare @MYTable Table ( RecordID int identity, EmployeeID int, JobCode varchar(4) ) Insert into @MYTable Values(1,'123') Insert into @MYTable Values(2,'123') Insert into @MYTAble Values (1,'123') Insert into @MyTable Values (2, '123') Insert into @MyTable Values (3,'123') Insert into @MYTable Values (1,'222') Insert into @MYtable Values (3,'222') Insert into @MYTable Values (1,'222') Insert into @MyTable Values (4,'222') Insert into @MyTable Values (5,'123') Insert into @MyTable Values (6,'123') Insert into @MyTable Values (7,'457')
Select * from @Mytable
The each employee can come to this table multiple time however the job code should be different for each every single record. So If I have to check this error and see which record has been repeated with the same Employee ID and JobCode and how many times. how would i do that Thanks,
Is there a simple command to find the 18th occurance/position of ";" in a field and I need to do this in the SQL code.
I can use charindex to find the 1st position and I can write a function to keep chopping off the data at the start of the field until I get to the 18th occurance but it takes 18 lines or so of code.
Is there a simpler way of doing this?
I need to find the 18th occurance of ";" and then take the next 4 characters.
I have a windows nt server 4.0 .its having IIS4.0 as well as Ms sql server 7.0 installed. I am using ASP ( active server pages) to store the fields entered by the customers in the order processing form in my site( thats the default page--form)
this form calls the asp file and asp file successfully stores the fields in the corresponding table.Now the problem is that how can i configure my sql server in such a way that sql server can mail me( as i am the administrator) informing the new order placed by the customer.
I know sql mail , and i have already configured that also. but how can i create the trigger for the table updation?
i tried using the enterprise manager, i was using xp_sendmail. but sql says "the stored procedure can't be found"
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
Here is the code. SELECT info1.categoria FROM ( SELECT DC2.categoria,count(DC2.ref) as quantidade FROM Disco_Categoria as DC2 GROUP BY DC2.categoria ) as info1 WHERE info1.quantidade > 2 AND info1.categoria != ALL ( SELECT DC2.categoria FROM Encomenda_Disco as ED2 INNER JOIN Disco_Categoria as DC2 ON ED2.ref=DC2.ref WHERE ED2.cod = 1 --ED.cod )
What I'm trying to do is to check if a category doesn't exist for a specific delivery (Encomenda). After reading about the predicated ALL, I decided that it was to be used, but it isn't returning the expected result.
Someone correct me if I'm wrong (since probably I am ;)) but what I'm asking in the code is to compare info1.categoria with all results of DC2.Categoria to check if there is any match, returning true if there isn't any match at all.
It is just not doing it. Can someone please point me at the problem so I can solve this?
Hello all.Is there a simpler way to write something like this:select column_a from tablewhere column_b = ( select max( column_b ) from table )ie. find the row where column_b is at it's maximum and return anothercolumn's value?something like (hypothetically)select column_a from tablewhere column_b is maxLeaving aside the issue of whether max( column_b ) is unique ...Thanks.Aaron
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
hey everyone, everyone here has been extremely helpful, I'm extremely appreciative. i have another question if anyone has the time.I want to pull the value of one column/row into a string, i know this value to be one int or 1 word under 10 characters. I'd like to be able to use this variable as a conditional, so my if/else statements have information to work off of. I have been using the following format in by code-behind pages to do my SQL insert/update/delete - however I cannot figure out how to SELECT and get those results into a string. I'm new obviously, so dumbed-down explanation would be greatly appreciated!This is what I've used so far for working with my DB:using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls; protected void Button1_Click(object sender, EventArgs e) { SqlDataSource profilesinsert = new SqlDataSource(); profilesinsert.ConnectionString = ConfigurationManager.ConnectionStrings["ProfilesConnectionString1"].ToString(); profilesinsert.InsertCommandType = SqlDataSourceCommandType.Text; profilesinsert.InsertCommand = "INSERT INTO ProfileComments (Approved) VALUES (@Approved)"; profilesinsert.InsertParameters.Add("Approved", "yes"); profilesinsert.Insert(); } The SELECT into a string I'd like to do on page_load, so that I can test that variable upon button click, and have a different value in "Approved" depending on the 1 int or small word result from my SELECT.THANK YOU very much to anyone who offers help! love you guys :)
I'm building a textbox with search functionality towards SQL 2000. The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column. The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better. Whats is the best way of doing this? Cheers! /Eskil
I have a table that is a very general layout so I can expand upon values later in the future easily. Basically it looks like this:
Code:
ID PlayerID Ability Score 1 1 STR 18 2 1 DEX 17 3 1 CON 16
What I'm trying to do is create a stored procedure where I pass in a PlayerID and it will return a result set of
Code:
STR DEX CON 18 17 16
It doesn't appear that there's a way in SQL Server 2000 to SELECT a column AS a variable. I suppose I could go about creating a temporary table, but seems like an odd work around to get it to work. Any suggestions? Or am I just missing something obvious?
