Select The First Occurance In A Column
Sep 18, 2001
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.
Thanks in advance for your help,
Jennifer
View 4 Replies
ADVERTISEMENT
Sep 20, 2004
Hey all:
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
Any help on this is greatly appreciated.
View 6 Replies
View Related
Sep 23, 2006
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?
View 2 Replies
View Related
Mar 26, 2008
Hi!
I have a problem with a sql script.
This little nice script works fine by itself.
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
View 4 Replies
View Related
Feb 22, 2008
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,
View 15 Replies
View Related
Mar 20, 2008
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.
View 4 Replies
View Related
May 17, 2008
Hi friends,
I have a two table with following fields, table names are tbl_userinfo, tbl_Property.
tbl_userinfo fields are
user_id name
1 dhin
2 Mike
3 sam
4 Red
tbl_Property fields are
prpty_Id User_Id Address
1 1 3CostalRoad
2 1 westbengal
3 2 Loasass
what i want to do is, if tbl_info User_id occures in tbl_property, i want to display that full info abt tbl_userinfo
after comparing two tables Expected result is
user_id name
1 dhin
2 Mike
Please help me how to do this
View 6 Replies
View Related
Dec 14, 2000
Hello all
my problem is like this
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"
so anyone who knows this topic , respond please
Regards
Shabu
View 1 Replies
View Related
Jul 20, 2005
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
View 5 Replies
View Related
Apr 9, 2008
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.
View 11 Replies
View Related
Apr 10, 2008
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 data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
View 14 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jan 2, 2004
i need help to get the value of a column which is selected from a column of another table....
View 3 Replies
View Related
May 19, 2015
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 (
[code]....
View 3 Replies
View Related
Feb 17, 2004
I want to know how to select a Column without selecting a column name
View 6 Replies
View Related
Sep 27, 2006
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?
View 2 Replies
View Related
Feb 8, 2008
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?
View 9 Replies
View Related
Aug 28, 2006
Is there a way to select a column number without knowing its name? For example I want to select column 3 of a file without knowing it is:
Select [column 3] FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:ReportsTest;','select * from Report.txt ')
View 5 Replies
View Related
Mar 29, 2006
How do I get all the entries in a column, but make it so that there is only one occurance of each unique item.
View 4 Replies
View Related
Jun 29, 2006
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?
Thanks in advance.
View 1 Replies
View Related
Oct 6, 2006
hi,i have
q_no process mech instru comm budget
q3malusasimalusasimalu
q5sasimalusasimalusasi
i want to select all the column names where q_no=q3
View 4 Replies
View Related
Feb 14, 2006
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
View 7 Replies
View Related
Apr 17, 2006
Hi,
I have a table with 100 column i want to select 99 column except one without writting name of all the 99 columns in the select statement.
View 5 Replies
View Related
Dec 20, 2007
Hi,
I have the following tables.
Code Block
CREATE TABLE [Liga] (
[cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[tronco] [int] NULL ,
[nrtelefone] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
1111 10 89543964
1111 9 32659841
1111 8 32569874
1111 7 25469874
CREATE TABLE [Lista] (
[cod_cliente] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[troncos] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
'1111' '8,10'
For this data what I need to do is return all rows from "Liga" that the value of the column Tronco is in troncos from the table Lista
So for example if I query this data the return would be
1111 8 32569874
1111 10 89543964
And so on...
How can I do that ?
View 7 Replies
View Related
Sep 24, 2006
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.
View 1 Replies
View Related
Jun 18, 2007
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 :)
View 19 Replies
View Related
Jul 17, 2007
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
View 3 Replies
View Related
May 14, 2008
I've looked on line, but can't figure out how to select the first fifty characters of a varchar field in sql. (this seems so easy).. argh..
View 1 Replies
View Related
Jun 23, 2004
I swear I have done this before and can't remember how.
I want to select columns in a SELECT statement by their number instead of name.
I have looked everywhere for the syntax.
Also, is there a function to 'count' the number of columns in a table?
Thanks.
View 2 Replies
View Related
Sep 29, 2004
Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
DECLARE @SQLStatement varchar(255)
SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')'
EXEC(@SQLStatement)
GO
I get the following error:
Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int
The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
UPDATE
myTable
SET
Boot3 = @VoteID
WHERE
PlayerID = @PlayerID
GO
Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!
View 2 Replies
View Related
Aug 19, 2002
In other words, rather than read a column vertically,
select one record with a comma delimited list of all data values in the
column?
Thanks!
View 1 Replies
View Related
Feb 13, 2005
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?
View 3 Replies
View Related
