I have a query:
SELECT *
FROM Mobile_Subscriber
WHERE (sub_ID IN (17, 2, 19))
The result return:
sub_ID sub_name
2 John
17 Alice
19 Eddy
But what I want is:
sub_ID sub_name
17Alice
2John
19Eddy
Is it possible to return the result order by the query with: sub_ID IN (17,2,19)?
I try to search my data and sort the result by importance.
I'm using a MS Access database and my data (table1) looks like this:
Code:
ID NAME TEXT
1 Apples Good red apples
2 Bananas Fine yellow bananas
3 Yellow apples Great yellow apples
I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:
Code:
SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*'
UNION
SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*'
ORDER BY searchorder
The output is this:
Code:
ID NAME TEXT SEARCHORDER
3 Yellow apples Great yellow apples 1
2 Bananas Fine yellow bananas 2
3 Yellow apples Great yellow apples 2
So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.
Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?
Best regards,
Peter from Denmark
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
Trying to get data from result of MDX query in dec order:
iLoopFrom = cst.Axes(1).Positions.Count - 1
iLoopTo = 0
iLoopStep = -1
im using these lines to read the data from bottom to top but what exactly does Position.Count do? and is the -1 there because the result has headers??
Thanks
I'm trying to find a way to sort my query like the following:
SELECT * FROM tbl_Post WHERE ID = 3 OR ID = 1 OR ID = 4 OR ID = 2 ORDER BY (3,1,4,2)
Now if ID is my primary key, the sort by default would be 1, 2, 3, 4.
I would like to specify order so that I could return the order as 3, 1, 4, 2 if I wanted to.
Is there any way to do this?
Thanks,Russ
Dear All,
i need your help,
i had created a table student, studentid column with alpha numeric primary key with varchar datatype
now my problem: i want to sort the student id accroding to studentid like
STU1
STU2
STU3
STU4
STU5
.
.
.
STU9
STU10
STU11
but i’m getting the sorted result like this, how to overcome this problem,guide me PLEASE
STU1
STU10
STU11
STU12
.
.
.
STU100
.
.
.
STU1000
STU10000
STU2
STU20
STU200
STU2000
STU20000
STU20001
Thank's In Advances
Hello everyone,
I Have a table with columns:
- CustomerID
- DateTransactionCompleted
- AmountPaid
Illustration data as follow
GO
INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-12-2004','18000.00')
INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-15-2004','2000.00')
INSERT INTO CUS_Tab_Dev_2 VALUES(30,'1-16-2005','15000.00')
INSERT INTO CUS_TAB_DEV_2 VALUES(42,'2-2-2005','12000.00')
GO
What I want is a report that tells me the Total Sales per month Per Customer in the following manner:
Customer ID Month/Year Total Per Month
----------- ----------- ----------------
30 12-2004 20000.00
30 1-2005 15000.00
42 2-2005 12000.00
This is a homework, and I have been trying to get it to work since this morning. Any help is appreciated.
PS: If you do not feel you want/should help. Please don't call me names! I don't ask people to help me with my homework usually. But, I am running out of time on the assignment.
I have three tables:tblBook has the fields bookID, bookRangeID, bookSubjectID, bookCodetblBookRange has the fields bookRangeID, bookRangeDescriptiontblBookSubject has the fields bookSubjectID, bookSubjectDescriptionso some typical data in tblBook might be:1, 1, 1, B1HBSCI2, 1, 2, B2HBFRE13, 1, 3, B3HBGER4, 2, 1, B4PBSCI5, 2, 2, B5PBFRE6, 2, 3, B6PBGER7, 3, 1, B7CDSCI8, 3, 2, B8CDFRE9, 3, 3, B9CDGER110, 3, 3, B10CDGER211, 1, 2, B11HBFRE2tblBookRange would be:1, HardBack2, PaperBack3, CD RomtblBookSubject would be:1, Science2, French3, GermanI'd like to create a query which will return me the subjects along thetop, the book range down the side, and the bookcodes in the cells, abit like this:BookRange , Science, French, GermanHardBack , B1HBSCI, B2HBFRE1 B11HBFRE2, B3HBGERPaperBack , B4PBSCI, B5PBFRE, B6PBGERCD Rom , B7CDSCI, B8CDFRE, B9CDGER1 B10CDGER2Does that make any sense? So basically I'd like to get some kind ofdynamic SQL working which will do this kind of thing. I don't want tohard code the subjects in or the book ranges. I get the feeling thatdynamic SQL is the way forward with this and possibly using a cursor ortwo too, but it got quite nasty and convoluted when I tried variousattempts to get it working. (one of the ways I tried included workingout each result in a dynamic script, but it ran out of characters asthere were too many "subjects".)If anyone has any nice but quite dynamic solutions, I'd be delighted tohear.(and I know some of you have already told me you don't like tablesbeginnig with tbl, but I'm not hear for a lecture on namingconventions, I'm hear to learn and share ideas :o) )
View 2 Replies View RelatedI have a field called "type" in my "school" table that can have threepossibilities:ElementaryMiddleHighIn my result set, I always want Elementary to come first, Middle tocome second, and High to come third. An alpha sort gets me this:ElementaryHighMiddleSure, I could create a look-up that assigns an integer to the "type"field to get the right sort order (i.e. elementary = 0, middle = 1,high = 2) ... but is there a better way to do this with SQL syntax?Thanks,Ralph NobleJoin Bytes!
View 1 Replies View RelatedI want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View 4 Replies View RelatedI am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Hi Everyone,I am creating a portal and want the user to be able to select four variants from four separate drop down boxes... Such as chapter, story, etc... The user will then be able to click Find and shorten up the gridview list. The below query is what I am using for my current gridview (with custom paging). What I want to do is something like this: Pass a variable such as @Story. If no Story is chosen, then the variable would be *. If a variable is chosen, the the @Story would the StoryID. However, I don't believe SQL recognizes the * in this case. I was thinking it should, but I don't believe it does. I was hoping it would just take it and I could write, SELECT * from Stories where StoryId=@Story, or something like that... and if * was @Story, then it would just select all... or if it was 1, then just stories with a StoryId of 1.Am I totally off base here? Thanks! CREATE PROCEDURE SortAllStories@startRowIndex int,@maximumRows int,@totalRows int OUTPUTASDECLARE @first_id int, @startRow intSET @startRowIndex = (@startRowIndex - 1) * @maximumRowsIF @startRowIndex = 0SET @startRowIndex = 1SET ROWCOUNT @startRowIndexSELECT @first_id = StoriesID FROM Storie ORDER BY StoriesIdPRINT @first_idSET ROWCOUNT @maximumRowsSELECT Stories.StoryId, Chapters.ChapterName, Chapters.EnglishName, Translations.Translation,Stories.Verse, Stories.StoryFROM Chapters, Stories, TranslationsWHERE Chapters.ChapterId=Stories.ChapterId AND Translations.TranslationId = Stories.TranslationId AND Stories.StoryId >= @first_idORDER BY Stories.StoryIdSET ROWCOUNT 0SELECT @totalRows = COUNT(StoryId) FROM Stories
View 3 Replies View RelatedI have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
Hi,
Anyone can help me for the Sql query?
I want to sort a table with a priority column, e.g. in the following...
Table A
======
value
======
9
3
1
7
4
======
After sorting:
Table A
========
no value
========
1 1
2 3
3 4
4 7
5 9
========
Anyone can help me?
Thanks.
Daniel.
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies View RelatedI have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
which have a lot of impact to database performace since it do not fit onto memory and spill over to disks.
The question is when i ran a profiler and tried to catch a sort warning i cannot find the query which cause the alert.
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.
I am using sql server 2008.
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
Thanks
Amit K Patel
this data. need help
Sort following numbers by asc and desc order
Before query sort
-1.1
-8.8
-15.5
0.0
+0.5
+0.2
Sort asc
+0.5
+0.2
0.0
-1.1
-8.8
Sort Desc
-8.8
-1.1
0.0
+0.2
+0.5
Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
Hi to all,I just need to get two fields from a table and manipulate the resultsin next query of a procedure.I planned to code like what you seebelow,create procedure marks1as@ sql1 as varchar(50)@ sql1=select registerno ,subjectcode from mark;beginselect * from marksetting where registerno='@sql1.registerno' andsubjectcode='@sql1.subjectcode';endcan it be possible to get the results as shown in the code? elsepropose an alternative for this scenario.Thanks in Advance.
View 4 Replies View Relatedmail_delivery_master----------------------ml_id ml_from_mail_id ml_to_mail_id ml_user_id ml_subject ml_content ml_file_attached ml_no_of_file_attached ----------- ---------------------------------------- -------------- ------------ --------------------------------------------------------------------------------------------------------------------------------1 aa bb cc dd ee 2 joe@hotmail.com tt@gmail.com, JOHNYJP Forum answer somebody answer 0 1 3 joe@hotmail.com mailme@hotmail.com, JOHNYJP Forum answer somebody answer -1 0 4 joe@hotmail.com mailme@hotmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 5 joe@hotmail.com janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 6 joe@hotmail.com dff@gg.com, JOHNYJP Forum answer somebody answer 0 0 7 joe@hotmail.com tt@gmail.com,janani@ajsquare.net, JOHNYJP Forum answer somebody answer 0 0 8 Durai@gamial.com yogesh@gamail.com durai test test 0 0 9 janani@ajsquare.net devi@ajsquare.net janu test hi 0 0 10 janani@ajsquare.net devi@ajsquare.net JANANI test Miss u -1 2 mail_attachementsml_id ml_file_name ml_file_format ml_file_size ml_file_select_from ----------- ---------------------------- -------------- ------------ ------------------------------------------ 1 chocolate .jpg 114528 F: empchocolate.pjg 2 Tiger Caspian Blue.jpg .jpg 1114407 F:imageTiger Caspian Blue.jpg 3 Autumn.jpg .jpg 66287 F:imageAutumn.jpg 4 Ascent.jpg .jpg 63244 F:imageAscent.jpg 5 Azul.jpg .jpg 61365 F:imageAzul.jpg 6 daisy.jpg .jpg 8197 F:imagedaisy.jpg 7 Stonehenge.jpg .jpg 59600 F:imageStonehenge.jpg i want the result :ml_id , ml_from_mail_id, ml_to_mail_id from mail_delivery_master ,,,, and sum(ml_file_size) for that mail from mail_attachements give me the code solution regards samuel chandradoss
View 2 Replies View RelatedHi thereI want a stored procedure that contains a Select query. I want SP that return result of query(rows)how could i do this?please help
View 2 Replies View RelatedHi,all,
I ran the two queries and I thought it would be the same, but it's different.
Can you explain to me.
Query 1: result---52 rows
select s.InsuredSurname, s.email from studyUSA s
join interMedical I on s.email=I.email
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and (s.agentcode not like '162%') and (s.agentcode not like '17%')
and s.agentcode <> '130844'
Query 2: result--14 rows
select s.InsuredSurname, s.email from tis_studyUSA s
where convert(char(10), s.enrolldate, 126)>= '2004-01-01'
and convert(char(10), s.enrolldate, 126) <='2005-05-20'
and s.email IN (Select I.Email from tis_InterMedical I)
and (agentcode not like '162%') and (agentcode not like '17%')
and agentcode <> '130844'
Thanks!
Betty
hi,
i've tried searching on this but not having much luck, i'm trying to use the result of one and use it in another. is there any way to do this?
So for example, i would use the result of this query and store it in a variable called @tmp_id (if possible)
SELECT TOP 1 ID FROM tblWines WHERE RefNo LIKE 'AB1234';
then use that variable in another query
INSERT INTO tblLogWines (WineID, Date) VALUES (@tmp_id, now());
i've simplied the context to make it a little more understandable, any help is appreciated.
goran.
I have 1 table "Progress"P_no b_no status build_date----------------------------------------------------------------25 1 First_slab 2006/4/525 1 second slab 2006/5/625 2 first slab 2006/1/225 2 third slab 2006/2/3o/p should be asPno,bno, status, max(build_date)sample o/p can be as below25 1 second slab 2006/5/625 2 third slab 2006/2/3Thanks in Advance.
View 4 Replies View RelatedHello all,I tryed to simplify the problem as much as possible. I'll start with theDDL:----------------------------------CREATE TABLE #MyTable(NoID INT,Type CHAR,DateTransaction DATETIME)INSERT INTO #MyTable (NoID, Type, DateTransaction)SELECT 1 AS NoID, 'A' AS Type, '2004-01-01' AS DateTransaction UNION ALLSELECT 2, 'C', '2004-01-01' UNION ALLSELECT 3, 'B', '2004-01-01' UNION ALLSELECT 4, 'C', '2004-01-02' UNION ALLSELECT 5, 'B', '2004-01-02' UNION ALLSELECT 6, 'C', '2004-01-02' UNION ALLSELECT 7, 'A', '2004-01-03' UNION ALLSELECT 8, 'B', '2004-01-03' UNION ALLSELECT 9, 'A', '2004-01-03' UNION ALLSELECT 10, 'C', '2004-01-03' UNION ALLSELECT 11, 'B', '2004-01-03'----------------------------------What I want is all the same Type which, for a same DateTransaction, as adifferent Type inserte beetween them when data is sorted by DateTransactionand NoID. In this case I would like:Type DateTransaction------ -----------------C 2004-01-02 /* B is between two C (NoID = 5) */A 2004-01-03 /* B is between tow A (NoID = 8) */B 2004-01-03 /* A and C are between two B (NoID = 9 and10) */All of these have for the corresponding date at least one transaction with adifferent type between.In the real situation NoID is an autoincrement field, the PK. And theDateTransaction hasn't any time, just a round date.Any suggestion?Thanks for your time.Yannick
View 7 Replies View RelatedGood day.
I have a working knowledge of T-SQL but apparently not sufficient to solve my problem.
I have 3 tables defined as:
CREATE TABLE [dbo].[Complaints](
[ComplaintId] [int] IDENTITY(1,1) NOT NULL,
[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[DateCreated] [smalldatetime] NOT NULL,
[DateReceived] [smalldatetime] NOT NULL,
[Classification] [tinyint] NOT NULL,
[Misconduct] [tinyint] NOT NULL,
[Status] [tinyint] NOT NULL,
[OccurrenceDateTime] [smalldatetime] NOT NULL,
[OccurrenceCityTown] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[OccurrenceRegion] [tinyint] NOT NULL,
[OccurrenceCountry] [tinyint] NOT NULL,
[CreatorId] [int] NOT NULL,
[CreatedFrom] [tinyint] NOT NULL,
[Flags] [tinyint] NOT NULL,
[Summary] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,
[Comments] [varchar](8000) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_Complaints_1] PRIMARY KEY CLUSTERED
(
[FileNumber] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[ComplaintSubjects](
[ComplaintSubjectId] [int] IDENTITY(1,1) NOT NULL,
[FileNumber] [varchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[SubjectId] [int] NOT NULL,
[SubjectType] [tinyint] NOT NULL,
[SubjectIdentity] [tinyint] NOT NULL,
CONSTRAINT [PK_ComplaintSubjects] PRIMARY KEY CLUSTERED
(
[ComplaintSubjectId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Persons](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[MiddleNames] [varchar](64) COLLATE Latin1_General_CI_AS NULL,
[LastName] [varchar](32) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressNumber] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
[AddressStreet] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressCity] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressRegion] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressCountry] [varchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[AddressPostalZip] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[PhoneHome] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneWork] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneMobile] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[PhoneFax] [varchar](25) COLLATE Latin1_General_CI_AS NULL,
[DateOfBirth] [smalldatetime] NOT NULL,
[Status] [tinyint] NOT NULL,
[Type] [tinyint] NOT NULL,
[DateAdded] [smalldatetime] NULL,
CONSTRAINT [PK_Personnel_1] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Basically, I track complaints and each complaint can have 1 or more subjects associated with it.
The structure is the complaint is referenced in a complaintsubject row and I use the persons table to find out detail of the complaintsubject (firstname, middlename, lastname) complaintsubject just holds Id's to normalize the database rather than have all details of persons table copied into every complaint (pretty basic so far).
My first question is regarding structure...since my complaint to complaintsubject relationship is 1 to many I assume the best way to associate the two is to have a FileNumber field in the complaintsubject linking back to a complaint. Is there any other design that is better suited to this type of relationship?
Second question and most important: I need a way to search by a string that appears in any part of the firstname, middlenames, lastname. I have the query for this and its working. But the problem arises when I have more than one match (i.e. 2 or more people are involved in the complaint where the match string fits both - example say we have a match string of 'an' and in complaint xyz 2 people involved are Sandy Blah and Andy Blue). Now when I do my join to pull out the complaints that involve any people with a name containing 'an' the result set returns the same complaint twice, one for Sandy Blah and the other for Andy Blue). Since I am only interested in certain complaint details and not interested in having the actual people involved returned in the result set, I would like to return only 1 row for each complaint when more than one match is found for the firstname,middlenames,lastname. I could live with having the duplicate rows and process them easily application side, but I am sending a lot of duplicate information over the wire for nothing and would like to optimize this.
I hope I have described this well enough and would greatly, greatly appreciate any help you can provide.
Best Regards.