using sql query.......

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

the code is

SQL

CREATE PROCEDURE [dbo].[spFetchAlbumList]
(
@AlbumName  NVARCHAR(50)=null,
@UserGUID   UNIQUEIDENTIFIER=null,
@AlbumGUID  UNIQUEIDENTIFIER=null
)
AS
BEGIN
    SET NOCOUNT ON;
   
     
  SELECT   UserGUID,AlbumGUID,(AlbumImage),AccordinStatus,CreatedDate FROM tblAlbum 
  WHERE    AlbumName=@AlbumName AND UserGUID=@UserGUID AND AlbumGUID=@AlbumGUID  


UNION ALL

SELECT  UserGUID,AlbumGUID,(AlbumImage),AccordinStatus,CreatedDate FROM tblAlbum 
  WHERE AlbumName=@AlbumName AND UserGUID=@UserGUID  AND   AlbumGUID!=@AlbumGUID  


    END

i got the answer :

AlbumImage	                       AccordinStatus
cartoon.png.png	                             6
cartoon.png	                             1
_hccABq.png	                             2
Chrysanthemum.jpg.png	                     3
Desert.jpg.png	                             5
Koala.jpg.png	                             8

But I need the answer following type:

AlbumImage	                AccordinStatus
cartoon.png.png	                      6
Koala.jpg.png                         8
Desert.jpg.png                        5
Chrysanthemum.jpg.png                 3
_hccABq.png                           2
cartoon.png                           1

[edit]Code block added - OriginalGriff[/edit]

Posted 25-May-15 19:39pm

aiswarjya

Updated 25-May-15 20:02pm

OriginalGriff

v3

Add a Solution

Comments

RossMW 26-May-15 1:59am

What are you trying to do. Also remember we can not see your data to see the effect of the query. The where clause (AlbumGuid ) seem to cancel themselves out so you end up with everything

Mehdi Gholam 26-May-15 2:05am

The answer is the same only ordered differently.

OriginalGriff 26-May-15 2:08am

Clearly you want to use an ORDER BY clause in there - but from the sample data you provide, I can't see what the heck you are trying to sort by - it's not alphabetical by image, and it's not by status - so You need to think a bit about exactly what you are trying to achieve and explain it rather better, bearing in mind that we can't see your screen, access your HDD, or read your mind.
And as RossMW says, you queries cancel each other out, so I suspect that what you are trying to do is not related to that query at all.
Perhaps showing us the sample data you are querying from and explaining what output you need would help, instead of that rather odd query?
Use the "Improve question" widget to edit your question and provide better information.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-05-25T20:31:00

Solution 1

As OriginalGriff[^] mentioned in the comment to the question, you have to use ORDER BY clause[^].

Note, you should use more efficient query, such as:

SQL

SELECT   UserGUID, AlbumGUID, (AlbumImage), AccordinStatus, CreatedDate
FROM tblAlbum
WHERE AlbumName=@AlbumName AND UserGUID=@UserGUID -- AND (AlbumGUID=@AlbumGUID OR AlbumGUID!=@AlbumGUID)
ORDER BY AccordinStatus DESC

Think of it!

Posted 25-May-15 20:31pm

Maciej Los

Comments

aiswarjya 26-May-15 3:48am

i clicked one image i get it first the particular image.after that i need next image using query.. but i get last image...

Maciej Los 26-May-15 4:06am

What? I already answered your question about sort order. If you have another question, feel free and post it on CP Quick Answers board.

aiswarjya 26-May-15 4:54am

if i used "ORDER BY AccordinStatus DESC" then it sorted all the rows. but i don't want this..

Maciej Los 26-May-15 5:04am

You should know that the answer is as good as the question. You did specify that the problem is in sorting. Now, you're saying that you "don't want this". I'm bit confused ;(

aiswarjya 26-May-15 5:09am

the question is that
I have a 6 images.first i clicked 3rd image.that time i saw 3rd image after that i saw last image sequentialy.but i want to saw 4th image that time.

if i will use sort command all the rows have been sorted.

Maciej Los 26-May-15 15:00pm

See solution 2 ;)

Maciej Los · Answer 2 · 2015-05-26T09:00:00

According to OP's comments...

You're looking for something similar to pagination[^].
See also these articles:
Paging Through a Query Result[^]
Paging SQL Server result sets[^]

I'd suggest to use Linq to achieve functionality you're looking for. Have a look at below code:

C#

void Main()
{
	//create DataTable object
	DataTable dt = CreateMyDataTable();
	//display 1. image
	DisplayNextImage(dt, 0);
	//display 3. image
	DisplayNextImage(dt, 2);
}

// Define other methods and classes here
public DataTable CreateMyDataTable()
{
	DataTable dt = new DataTable();
	DataColumn dc  = new DataColumn("AlbumImage", Type.GetType("System.String"));
	dt.Columns.Add(dc);
	dc  = new DataColumn("AccordinStatus", Type.GetType("System.Int32"));
	dt.Columns.Add(dc);
	dt.Rows.Add(new Object[]{"cartoon.png.png", 6});
	dt.Rows.Add(new Object[]{"cartoon.png", 1});
	dt.Rows.Add(new Object[]{"_hccABq.png", 2});
	dt.Rows.Add(new Object[]{"Chrysanthemum.jpg.png", 3});
	dt.Rows.Add(new Object[]{"Desert.jpg.png", 5});
	dt.Rows.Add(new Object[]{"Koala.jpg.png", 8});
	return dt;
}

//display image
public void DisplayNextImage(DataTable dt, int current)
{
	var qry = dt.AsEnumerable().Skip(current).Take(1);
	foreach(var row in qry)
	{
		Console.WriteLine("{0}\t\t{1} ", row.Field<string>("AlbumImage"), row.Field<int>("AccordinStatus"));
	}
}</int></string>

Result:

cartoon.png.png    6
_hccABq.png    2

For further information, please see this: Page large result sets with LINQ[^]